Calculate interest.

bendad2112

New Member
Joined
Nov 5, 2004
Messages
25
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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

brettdj

Active Member
Joined
Feb 5, 2003
Messages
426
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

New Member
Joined
Nov 5, 2004
Messages
25
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

Active Member
Joined
Feb 5, 2003
Messages
426
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

Well-known Member
Joined
Jul 14, 2003
Messages
1,017
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,552
Messages
5,636,975
Members
416,953
Latest member
broexc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top