I want to write an excel formula that will allow me to evaluate the real interest rate of return on a loan where the capital depreciates whilst the interest payable remains that payable on the original sum.

Capital 5000

Annual interest rate 5% paid monthly

Monthly Capital Repayment 500

Thus 10 capital repayments of 500 and 10 interest payments of 250

The capital is paid back at the rate of 500 per month thus

month 1 5000-500=4500 Annual rate of interest payable @5% on initial 5000

Month 2 4500-500=4000 Annual rate of interest payable @5% on initial 5000

Month 3 4000-500=3500 Annual rate of interest payable @5% on initial 5000

If the

annual interest rate is 5%, the

monthly interest payment is about

20.83 (5000*5%/12), not 250. Conversely, if there are 10

monthly interest payments of 250 (5000*5%), 5% is the

monthly interest rate. The

annual interest rate is 60% (12*5%). I will assume the latter.

What you describe is a repayment plan where both the principal and interest payments are fixed amounts; a total monthly payment of 750. The following amortization schedule shows the rate of interest based on the balance in column E.

[TABLE="class: grid, width: 400"]

<tbody>[TR]

[TD="width: 64"]

[/TD]

[TD="width: 64, align: center"]

A

[/TD]

[TD="width: 64, align: center"]

B

[/TD]

[TD="width: 64, align: center"]

C

[/TD]

[TD="width: 64, align: center"]

D

[/TD]

[TD="width: 64, align: center"]

E

[/TD]

[/TR]

[TR]

[TD="width: 64, align: right"]

1

[/TD]

[TD="width: 64"]

Loan

[/TD]

[TD="width: 64, align: right"]

5000[/TD]

[TD="width: 64"]

[/TD]

[TD="width: 64"]

[/TD]

[TD="width: 64"]

[/TD]

[/TR]

[TR]

[TD="align: right"]

2

[/TD]

[TD]

Prin Pmt[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

for[/TD]

[TD="align: right"]

10[/TD]

[TD]

months[/TD]

[/TR]

[TR]

[TD="align: right"]

3

[/TD]

[TD]

Int Pmt[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

at[/TD]

[TD="align: right"]

5%[/TD]

[TD]

monthly[/TD]

[/TR]

[TR]

[TD="align: right"]

4

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[/TR]

[TR]

[TD="align: right"]

5

[/TD]

[TD="align: right"]

Pmt#[/TD]

[TD="align: right"]

Prin Pmt[/TD]

[TD="align: right"]

Int Pmt[/TD]

[TD="align: right"]

Bal[/TD]

[TD="align: right"]

Int Rate[/TD]

[/TR]

[TR]

[TD="align: right"]

6

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[TD]

[/TD]

[TD="align: right"]

5000[/TD]

[TD]

[/TD]

[/TR]

[TR]

[TD="align: right"]

7

[/TD]

[TD="align: right"]

1[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

4500[/TD]

[TD="align: right"]

5.00%[/TD]

[/TR]

[TR]

[TD="align: right"]

8

[/TD]

[TD="align: right"]

2[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

4000[/TD]

[TD="align: right"]

5.56%[/TD]

[/TR]

[TR]

[TD="align: right"]

9

[/TD]

[TD="align: right"]

3[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

3500[/TD]

[TD="align: right"]

6.25%[/TD]

[/TR]

[TR]

[TD="align: right"]

10

[/TD]

[TD="align: right"]

4[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

3000[/TD]

[TD="align: right"]

7.14%[/TD]

[/TR]

[TR]

[TD="align: right"]

11

[/TD]

[TD="align: right"]

5[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

2500[/TD]

[TD="align: right"]

8.33%[/TD]

[/TR]

[TR]

[TD="align: right"]

12

[/TD]

[TD="align: right"]

6[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

2000[/TD]

[TD="align: right"]

10.00%[/TD]

[/TR]

[TR]

[TD="align: right"]

13

[/TD]

[TD="align: right"]

7[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

1500[/TD]

[TD="align: right"]

12.50%[/TD]

[/TR]

[TR]

[TD="align: right"]

14

[/TD]

[TD="align: right"]

8[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

1000[/TD]

[TD="align: right"]

16.67%[/TD]

[/TR]

[TR]

[TD="align: right"]

15

[/TD]

[TD="align: right"]

9[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

25.00%[/TD]

[/TR]

[TR]

[TD="align: right"]

16

[/TD]

[TD="align: right"]

10[/TD]

[TD="align: right"]

500[/TD]

[TD="align: right"]

250[/TD]

[TD="align: right"]

0[/TD]

[TD="align: right"]

50.00%[/TD]

[/TR]

</tbody>[/TABLE]

The formula in E7 is =C7/D6. Copy the formula down through E16.

It is unclear if that is the formula you are looking for.

The total interest percentage is =D2*B3/B1 (50%).

If you want to calculate the rate of interest based on balance without a table, the formula for the month number in A4 is:

=$B$3 / ($B$1-(A4-1)*$B$2)