Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Flexible loan calculator-additional borrowing, irregular payments | Excel Discussion (Misc queries) | |||
loan payments - Interest only? | Excel Discussion (Misc queries) | |||
Excel Template - Loan Calculator with Extra Payments | Excel Discussion (Misc queries) | |||
Add insurance to loan payments | Excel Worksheet Functions | |||
How do you set up a loan using the loan calculator w/odd payments. | Excel Discussion (Misc queries) |