You are not logged in.

- Topics: Active | Unanswered

Pages: **1**

**supergekko1****Guest**

Hi,

First post on this board, and this is a real life work problem I've got, that's doing my head in :? . I'm not a maths whizz by any means and any help on this would be gratefully received. To give a bit of background, I am building an Excel model on the profitability of various loans that our business makes. So, in basic terms the profitability of a loan will be the sum of future interest to be received less the interest paid on the money we borrowed to fund the loan. I'm not discounting for the time value of money or anything like that, just keeping it simple. So I am using the following equation to calculate the profitability:

((A x (R/12) x (1+R/12) x ((1+(R/12))^T))/(((1+(R/12)^T-A)-((A x (F/12) x ((1+(F/12))^T))/(((1+(F/12))^T)-1) x T-A)

where:

A = Amount of loan

R = Annual rate of interest paid by the customer

T = Term of the loan expressed in months

F = Annual rate of interest paid to fund the loan

Now that is all fine when I calculate the profitability of individual loans. But now I am trying to calculate the profitability of a portfolio of loans. For example take the following data:

1) Loan of £10,000 lent for 40 months at 10%, with a funding cost of 5%. Profit on deal is 923.07

2) Loan of £15,000 lent for 35 months at 6%, with a funding cost of 5%. Profit on deal is 236.64

3) Loan of £11,000 lent for 42 months at 12%, with a funding cost of 5%. Profit on deal is 1,511.94

Total profitability of these deals = £2,671.65

Now if I try to combine these loans, to give the total lent, a weighted average term and a weighted average rate, you get the following:

Total loans: £36,000

Weighted average term: 38.53 months

Weighted average rate: 8.94%

Weighted ave funding rate: 5%

Now if this was an individual loan I would expect the profitability of a deal with this profile to equal £2,671.65, i.e. the same as the individual loans. But no, when I put this through my formula I'm getting £2,507.65.

Can someone please put me out of my misery and tell me how it is possible to get a different answer to the sum of the individual loans please! A virtual bottle of wine goes to the person who gives me the right answer! Thanks for reading.

**bobbym****Administrator**- From: Bumpkinland
- Registered: 2009-04-12
- Posts: 105,720

Hi;

((A x (R/12) x (1+R/12) x ((1+(R/12))^T))/(((1+(R/12)^T-A)-((A x (F/12) x ((1+(F/12))^T))/(((1+(F/12))^T)-1) x T-A)

Have you copied the formula correctly? There are more left parentheses than right ones. There should always be the same amount.

**In mathematics, you don't understand things. You just get used to them.****If it ain't broke, fix it until it is.****No great discovery was ever made without a bold guess. **

Offline

Pages: **1**