# Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Payments

1. ## Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Payments

Hello Friends:

Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.  Register To Reply

2. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym Originally Posted by gurpreet161 Hello Friends:

Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

Monthly Rate
=RATE(360,0,195000,-331068.81)
0.14714%

Annual Rate
==RATE(360,0,195000,-331068.81)*12
1.7657%  Register To Reply

3. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym Originally Posted by gurpreet161 Hello Friends:

Please help me with a formula that can tell me how much interest rate (in percentage) I paid on this loan where I have the total interest paid over all monthly payments, original loan amount, and number of monthly payments made. At the end of the term of 360, all interest and principal was paid off.

I have given you an interest rate in the last reply

In your original post, you provided us with the loan amount, number of periods and interest paid on loan

Usually a loan amount, monthly payment and number of periods are known and one has to find the rate

Since a monthly payment was not given, I had to find the total amount that you repaid which includes the principal and interest

Now since time value of money equation looks this way

PV (1+RATE)^NPER + PMT (1+RATE*type)[ {(1+RATE)^NPER} - 1 ]/RATE + FV = 0

Thus you would need to have the known values for PV (loan amount), NPER (number of periods), PMT (monthly payment) and FV (salvage value) to solve for RATE (periodic rate)

But since we don't have periodic payment yet we do have the future value of the investment which is the amount you paid back along with the initial amount of investment the amount you borrowed along with number of period. This allows us to rewrite the TVM equation as follows

PV(1+RATE)^NPER + FV = 0

Where FV is the future value of periodic monthly payment plus the salvage value

Salvage value is zero and future value of periodic payment would be the loan amount plus the interest paid on it

FV = \$195,000 + \$136,068.81
FV = \$331,068.81

Putting the values in the formula above leads us to this

195,000(1+RATE)^360 + (-331,068.81) = 0
195,000(1+RATE)^360 - 331,068.81 = 0
195,000(1+RATE)^360 = 331,068.81
(1+RATE)^360 = 331,068.81/195,000
(1+RATE) = [ 331,068.81/195,000 ]^(1/360)
RATE = [ 331,068.81/195,000 ]^(1/360) - 1
RATE = 1.697788777^(1/360) - 1
RATE = 1.001471433 - 1
RATE = 0.001471433
--ERROR---RATE = 0.471433%
RATE = 0.1471433%
Annual RATE = 0.1471433% * 12
Annual RATE = 1.76572%

Now we are in a position to find your monthly loan repayment

RATE = 1.76572%
NPER = 360
PV = 195,000

=PMT (1.76572006%/12, 360, 195000)
(\$698.13)

That is almost \$700 per month for 30 years

P.S. Was this a homework assignment or did you really have a loan such as this  Register To Reply

4. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym

MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.  Register To Reply

5. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym Originally Posted by gurpreet161 MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
I am sorry to have made a error in typing

This 0.471433% * 12

is suppose to be this

0.1471433% * 12

So the actual annual rate 1.76572% is what I get for your data  Register To Reply

6. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym Originally Posted by gurpreet161 MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
At an annual interest rate of 5.05% your monthly payment comes out to (\$1,052.77)

EDIT---

Let me see what is wrong with the way I calculated the interest rate

Give me a minute  Register To Reply

7. ## Re: Calculating Interest Rate for Total Interest Paid, Loan Amount and No. of Monthly Paym Originally Posted by gurpreet161 MoneyMaker, I do not have a monthly payment and that's why so much work to establish the rate. Also, I just want to make sure your calculation is accurate.

A small error I see is in 0.471433% * 12 to calculate Annual Interest Rate. I show this coming to 5.66%.

Also, I have been charged close to 5.05% and this is the confirmation I was looking to get. Let me play around more with what you did and see may be there is a calculation mistake. You have been wonderful with some mathematic lesson here and gives me something to work with.
I am sorry to have performed the rate calculation under wrong assumptions

Your loan amount is \$195,000 and you state that interest paid on this amount is \$136,068.81 thus the total amount you repaid is \$331,068.81

The monthly payment would then be calculated by dividing the total amount paid by 360 thus your monthly payment is \$919.64

Loan amount = \$195,000
Interest Paid = \$136,068.81
Total amount repaid = \$331,068.81

Monthly payment = \$331,068.81 / 360
Monthly payment = \$919.64

Now that we have the monthly payment, we have all three items required to calculate the monthly rate

NPER 360
PMT \$919.64
PV \$195,000
RATE = (360, -919.64, 195000)
0.325%

Annual Rate 3.90%

If you state that the quoted rate is around 5.05% then something else is wrong with your numbers. As with an annual rate of 5.05% the total interest you pay back on a loan of \$195,000 would be

=CUMIPMT(5.05%/12,360,195000,1,360,0)
(\$183,996.86)

Where as with an annual rate of 3.9% the total interest paid back is
=CUMIPMT(3.89894899%/12,360,195000,1,360,0)
(\$136,068.81)  Register To Reply