# Calculate interest.

##### New Member
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?

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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

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

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

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.

Replies
1
Views
641
Replies
6
Views
289
Replies
5
Views
483
Replies
1
Views
490
Replies
3
Views
567

1,219,770
Messages
6,150,166
Members
450,937
Latest member
kattyg261

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