Loan calculator with extra payments
using the Loan Calculator template in Excel, I am trying to calculate loan
payments with optional extra payments, over 5 years. However, every time I input the optional extra payments, Excel shortens the loan term instead of reducing the monthly payment amount (which is what I want). Any suggestions? -- Sue W. |
Loan calculator with extra payments
Sue,
Make your own loan calculator: for this example, $100,000 30 year mortgage at 6%. In row 1, entere these headers in cols A to G Pmt # Note Amount (+) Term, Months Payment (-) Annual Interest Rate Principal Extra Payment Then in row 2, cols A to F, enter =ROW()-ROW($A$1) 100000 360 =PMT(E2/12,C2,B2) 0.06 =D2+B2*E2/12 In row 3, cols A to F =ROW()-ROW($A$1) =B2+F2-G2 =C2-1 =PMT(E3/12,C3,B3) =E2 =D3+B3*E3/12 Copy row 3 down for 358 rows, and enter any principal payments into col G (as a positive number). HTH, Bernie MS Excel MVP "jjbud44" wrote in message ... using the Loan Calculator template in Excel, I am trying to calculate loan payments with optional extra payments, over 5 years. However, every time I input the optional extra payments, Excel shortens the loan term instead of reducing the monthly payment amount (which is what I want). Any suggestions? -- Sue W. |
Loan calculator with extra payments
Bernie,
Thank you! I'll try it. -- Sue W. "Bernie Deitrick" wrote: Sue, Make your own loan calculator: for this example, $100,000 30 year mortgage at 6%. In row 1, entere these headers in cols A to G Pmt # Note Amount (+) Term, Months Payment (-) Annual Interest Rate Principal Extra Payment Then in row 2, cols A to F, enter =ROW()-ROW($A$1) 100000 360 =PMT(E2/12,C2,B2) 0.06 =D2+B2*E2/12 In row 3, cols A to F =ROW()-ROW($A$1) =B2+F2-G2 =C2-1 =PMT(E3/12,C3,B3) =E2 =D3+B3*E3/12 Copy row 3 down for 358 rows, and enter any principal payments into col G (as a positive number). HTH, Bernie MS Excel MVP "jjbud44" wrote in message ... using the Loan Calculator template in Excel, I am trying to calculate loan payments with optional extra payments, over 5 years. However, every time I input the optional extra payments, Excel shortens the loan term instead of reducing the monthly payment amount (which is what I want). Any suggestions? -- Sue W. |
All times are GMT +1. The time now is 04:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com