Forum Discussion

Kim Boyd's avatar
Kim Boyd
Copper Contributor
Nov 03, 2017

Loan Amortization Template

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.

 

Please help!!

Formulas & Functions
Formulas and Functions

3 Replies

  • Jihad Al-Jarady's avatar
    Jihad Al-Jarady
    MCT
    Nov 03, 2017

    Hi Kim,

     

    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

    • Kim Boyd's avatar
      Kim Boyd
      Copper Contributor
      Nov 03, 2017

      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!

      • Jihad Al-Jarady's avatar
        Jihad Al-Jarady
        MCT
        Nov 05, 2017

        Hi Kim

        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