Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Interest Paid w/ Additional Payments to Principle
I am trying to calculate the interest I would save if I were to pay an
additional amount toward the prinicple of a loan, a certain amount of times a year, starting at a particular month in the loan term. For example, Loan Amount = $100,000 Interest Rate = 6.375% Loan Start Date = 12/1/03 Additional Payment = $100 Number of Times Per Year = 12 Add. Payment Start Date = 12/1/08 Anybody have any ideas? -- Cheers, Ryan |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculate Interest Paid w/ Additional Payments to Principle
RyanH wrote...
I am trying to calculate the interest I would save if I were to pay an additional amount toward the prinicple of a loan, a certain amount of times a year, starting at a particular month in the loan term. *For example, Loan Amount = $100,000 Interest Rate = 6.375% Loan Start Date = 12/1/03 Additional Payment = $100 Number of Times Per Year = 12 Add. Payment Start Date = 12/1/08 First, let i denote the effective monthly interest rate 0.6375/12, or 0.0053125. The interest paid in any regular loan payment is always equal to effective periodic interest rate times the remaining balance. If your loan above were originally to be repaid over 30 years, then the regular loan payment is given by PMT(i,360,-100000) or 623.87. The interest portion of the first payment is given by IPMT(i,1,360,-100000) [this requires the Analysis ToolPak be installed] or 531.25, which also equals 100000*i. When you make additional principal payments, you decrease the principal balance remaining after the payment, so the interest portion of the next regular payment is less. Anyway, the starting point would be to determine the principal balance after the 11/1/2008 lone payment, which is the 60th payment, which is given by =FV(i,60,PMT(i,360,-100000),-100000) or 93475.24. If you were to pay 100 more every month after that until the loan were fully paid off, the number of subsequent payments is given by NPER(i,PMT(i,360,-100000)+100,-FV(i,60,PMT(i,360,-100000),-100000)) or 218.633703018029, which should be interpreted to mean 218 payments of 723.87 and one final payment of less than that. The principal balance after the first 60 payments of 623.87 and the next 218 payments of 723.87 is given by FV(i,218,PMT(0.06375/12,360,-100000)+100,-FV(i,60,PMT (0.06375/12,360,-100000),-100000)) or 456.74. That needs to be grossed up for interest to give the final payment of 456.74*(1+i) = 459.16. If you had stuck to the original loan payments of 360 monthly payments of 623.87, your payments would total to 360*623.87 = 224593.16. 100000 of that would be principal, so the total interest paid would be 124593.16. The alternative of 60 payments of 623.87, 218 payments of 723.87 and a final payment of 459.16 would total to 195695.00. Again, 100000 of that would be principal, so the total interest paid would be 95695.00. The nominal interest savings would therefore be 28898.16 and you would have paid off the loan in 23 years and 3 months ratherr than 30 years. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Very flexible calculator for you
I think this calculator at
http://www.notbadsoft.com/#pcalc will allow you to estimate any scenarios of additional payments to minimize the total sum of interests. EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to calculate my mortgage payments interest part? | New Users to Excel | |||
how do you calculate compound interest with multiple payments? | Excel Worksheet Functions | |||
Cumulated interest paid | Excel Discussion (Misc queries) | |||
HOW to calculate the interest paid between 2 custom periods? | Excel Worksheet Functions | |||
How to calculate total interest on 12 month loan with early payments | Excel Worksheet Functions |