Need help with the formula for a cost of funds report

A "weighted average cost of the whole debt portfolio" would involve getting daily balances for each debt instrument and daily interest rates for each debtor. This calculation is normally part of an integrated banking system and is not any different than other commercial loans. You could ask in the accounting or commercial loan dept. if you do not know the interest rate terms or loan balances.

Ok thanks for the info so far.

I have full access to all of the information on every loan on the system but I know practically nothing about accounting.

The integrated banking system you mentioned is what I'm working on replacing but it's a case of reverse engineering a lot of it and I can't seem to get the calculations to match up. Is there a standard formula for working this stuff out or does it vary between institutions? For example, if I provided you with a set of numbers - principles, all-in rates, terms, etc - would you be able to apply some specific formula and come up with the average weighted cost of funds? If so, can you tell me what it is?

And for reference, this is to be run on a monthly basis.

Thanks again.

The "formula" you seek is ultimately just an interest rate calculation. Cost of funds = balances x interest rates then aggregated. Banks use different methods to report internally (called 360/365 annualization) and there are slight differences depending on the borrower/lender but the math is still simple. If this is like the US bank's "Fed funds" rate (inter-bank borrowing) then you need to talk to someone in the accounting or operations area, they know how the interest is calculated. It sounds like your job is to "aggregate" the totals - I can't think of a better word!

Cheers Steve.

I reckon I've got the basic formula but I'm having difficulty transforming the all-in rate on the loans to an "Average adjusted yield". The only guy in accounts who seems to know anything about this suggested that it was a conversion from a 360 day basis to a 365 day basis (i.e. All-in/360*365) and this does work for a lot of them but some are still coming up with different numbers.

For example:

The all-in rate on one loan converts from 2.701 to 2.738514, but using the same formula another loan converts from 2.381 to 2.414069 when it should be converting to 2.413753.

The only difference I can see is that the second one matured during the month I'm reporting on while the first was active for the whole month. They were active for 19 and 30 days respectively during that month. I'm guessing that has some effect on the weighting but I can't say I've any idea how to reproduce it.

messagenews:cwb.googlegroups.com...

Sure it has an effect on weighting, the average is certainly weighted towards those loans that are outstanding longer. In the first example how do you know the correct number (rate?) should be 2.413753? I do not understand what the "all-in rate" is but if you know the actual interest paid to the borrower/lender and the average daily balances then a rate can be figured and a weighted average can be derived. it sounds if you understand enough to eventually figure it out!

messagenews:cwb.googlegroups.com...