# mathematical calculation in excel

#### Stclements

##### Board Regular
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

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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...

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

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)

Replies
8
Views
731
Replies
6
Views
200
Replies
0
Views
305
Replies
3
Views
469
Replies
4
Views
602

1,196,447
Messages
6,015,318
Members
441,888
Latest member
birexual

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back