Total Interest Formula: loan amt $5K, 4.64% APR, 174 mos.

[spoulos]

I'm building a report in a reporting tool that doesn't have many functions and I'm needing to get the total interest on a mortgage.

So if the loan amount is $ 5,000.00 with and interest rate of 5.00% (4.64 apr) for 174 months, so what would be the actual formulat to get 2,039.58 which I get from my excel spreadsheet.

The tool I have doesn't have any math functions so I'm trying to build the formulat manually and I just can get it right.
Thanks.

 

[stapel]

What formula are you using right now? What are its variables, and how is each defined?

You say that you can't get your formula "quite" right. How "close" do you feel that you are?

Please be complete. Thank you!

Eliz.

 

[spoulos]

Re: Total Interest Formula

I attached the excel spreadsheet that has the originally formulas.

I'm not looking to build the scheduled payment/interest amounts, I just want the total interest.
The formula I used which I believe is wrong was:

5000*((5.00/12)/(1-Power(1+(5.00/12) ,174)))

 

[Denis]

Re: Total Interest Formula

The rate used is 5% annual compounded monthly: where does your 4.64apr come from?

The interest cost of 2,039.58 is obtained this way:
monthly payment is calculated : 40.45736.....
result multiplied by 174
result reduced by amount borrowed of 5000

EDIT: just saw your last post;
> 5000*((5.00/12)/(1-Power(1+(5.00/12) ,174)))
Not familiar with Excel, but I think that calculates the monthly payment; let result = P
Then total interest = P * 174 - 5000 ; kapish?

 

[spoulos]

Re: Total Interest Formula

The spreadsheet is how it's currently being done and I can't or won't be able to use it.

So the only values I will have to work with are:
the amount 5,000
interest rate 5.00
Number of months 174.

From there I'm trying to get the 2,039.58.
So the last reply doesn't help because I wouldn't have the individual payment amount.

 

[Deleted member 4993]

Re: Total Interest Formula

Like Denis explained - first you calculate the monthly mortgage "payment" (P)from

\(\displaystyle P = A\cdot (1+r)^n \,\cdot \frac{r}{(1+r)^n \, - \, 1}\)

Then total interest paid interest "I" would be

\(\displaystyle I \, = \, P \cdot n \, - \, A\)

 

[Denis]

Re: Total Interest Formula

So the last reply doesn't help because I wouldn't have the individual payment amount.

WHY NOT? This is the individual payment amount: 5000*((5.00/12)/(1-Power(1+(5.00/12) ,174)))
Don't complicate something simple :shock: