mathematical calculation in excel

[Stclements]

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.

Thus if the original sum is £5000 at an interest rate of 10% means that the interest payable is 500 per annum
but if 10% of the capital was paid thus the outstanding capital is now £4500 but interest payable at 10% of the original sum means that £500 will be paid of a debt of £4500 meaning the real rate is 11.11% etc

 

[Caribeiro77]

Can you post an example of what you need? Create a small table end type the values manually... i'm shure someone arround here will help you...

 

[Stclements]

Can you post an example of what you need? Create a small table end type the values manually... i'm shure someone arround here will help you...

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

The example shows that whilst a normal loan would be ammortised so that the interest payable would fall in line with the declining capital outstanding, what I am trying to evaluate is the real rate of return if the capital declines as per the example but the interest rate remains payable at the same trate as the initial starting sum thus interest is always paid monthly at a rate of 5% annually on a capital sum of 5000

 

[joeu2004]

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.

	A	B	C	D	E
1	Loan	5000
2	Prin Pmt	500	for	10	months
3	Int Pmt	250	at	5%	monthly
4
5	Pmt#	Prin Pmt	Int Pmt	Bal	Int Rate
6				5000
7	1	500	250	4500	5.00%
8	2	500	250	4000	5.56%
9	3	500	250	3500	6.25%
10	4	500	250	3000	7.14%
11	5	500	250	2500	8.33%
12	6	500	250	2000	10.00%
13	7	500	250	1500	12.50%
14	8	500	250	1000	16.67%
15	9	500	250	500	25.00%
16	10	500	250	0	50.00%

<tbody>
</tbody>

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)