- last edited on
I am using the Loan Amortization Template and when the payment dates fill in it is in years - 12/1/17, 12/1/18, 12/1/19. I entered 5 years with 52 payments per year and total payments of 260. How do I get the payment dates to go weekly. I can't follow the automatic formula in the template.
11-03-2017 11:16 AM
First, you need to unprotected the sheet in order to modify the formula.
From Review menu --> changes group --> click on unprotect sheet
on cell B18 replace the exiting formula with this one: =IF(Pay_Num<>"",Loan_Start,"")
on cell B19 replace the exiting formula with this one: =IF(Pay_Num<>"",B18+7,"")
then fill/copy the cell B19 to B20:B300
please find the template attached
11-03-2017 11:35 AM
We do amortizations for different pay periods, some weekly, most bi-weekly or semi-monthly. I thought the template would calculate dates based on the entries in cells D7, D8 and J6. Do you mean I will have to change the formula every time based on pay periods?
Thanks for help!
11-05-2017 08:26 AM
I just modified the formula as you requested, anyhow, you can modify it according to your need.
the number 7 means week "every 7 days", if you need it semi monthly you can add 15,..etc
I hope that helps you