Calculate interest.

[bendad2112]

I am trying to correct some loans issued from a 401k plan that had the wrong interest rate. My best approach is to mimic how the incorrect calculation worked and then just change the interest rate. The data is as follows:

Initial loan- $13744
interest rate- 5.5%
Total expected interest-2032.40
Term 5 years.

I cannot determine how an automated system calculated the expected interest to be 2032.40. If I knew this then I could change he calculation to have an interest rate of 5.75%. Any calculator wiz out there?

 

[brettdj]

That amount of interest doesn't make any sense unless the loan was being amortised (reduced with payments) over the 5 years

If 13744 was loaned at 5.5% for five years total interest
= 13744*(1+5.5%)^5-13744
= 4218

Simple interest paid annually fives time
= 13744 * 5.5%*5
= 3779.6

I used FV and goalseek to work out a monthly repyament schedule whereby the total interest @ 5.5% paid over 5 years on an initial $13,744 loan would be 2032.40

The goalseek returned $33.50 as the monthly payment, ie

=FV(5.5%/12,60,-33.5,13744)
= 15,766
interest = 15766-13744
= 2032

Cheers

Dave

 

[bendad2112]

Perhaps I did not provide enough information. These loans are amortized over the course of the loan term. For the example I provided, Here is the important information.

Loan amount=$13774.00
Loan term=5 years
Payment amount= $131.72
Expected total interest=$2032.40
Interest rate=5.5%

I need to find a calculation that would give me both the expected amount of interest and the payment amount if all I had to input was the loan amount, the term and the interest rate. I have an automated system that does this calculation for me, however I have a number of loans that were issued at the wrong interest rate. If I know hwo to calculate it, then I can just change the interest rate for a new calculation.

As a side note, is it possible to create the actual amortization schedule for this calculation as well.

Thanks

 

[brettdj]

Hi,

I seem to have a problem with HTML maker so I can't paste my sheet

From your amortisation rate I'm guessing that you pay off the loan twice a month or therabouts. You can calculate what your total regular payment is using PMT, ie

=PMT(5.5%/(5*24),5*24,13744)
= 131.13

This means 120 bi monthly payments of $131.13 will pay off the original $13,744 loan at a 5.5% interest rate

This regular payment can be broken into its interest and principal components using IPMT and PPMT

Put the numbers from 1 to 60 into cells A1 to A60
In B1 put
=IPMT(5.5%/24,A1,5*24,13744)
In C1 put
=PPMT(5.5%/24,A1,5*24,13744)
and copy down to B120 and C120

The total of the Principle column (C1:C120) is 13,744
The total of the Interest column (B1:B120) is 1,992

And ignore the last comment I made in the previous post, it wasn't relevant to what you are after.

Cheers

Dave

 

[Chitosunday]

bendad2112,

The 2,032 interest will be closer if the payment is every two months. Are you using actual no. of days per year ? The variance is quite big if the cause is just rounding off.