Skip to main content
Adaptive Insights
Knowledge and Support - Adaptive Insights

Formula Examples: Loan Amortization in a Modeled Sheet

Provides detailed examples of loan amortization formulas in a modeled sheet.

This topic shows you how to create an amortization schedule for multiple loans. It solves the following amortization issues:

  • Contractual fixed principal payment loans
  • Amortizing multiple loans and adding new ones easily
  • Interest Payment calculations for income statement
  • Loan Balances for Balance Sheet
  • Fixed, Variable and ARM loans
  • Different Payment frequencies for various loan types

It's best to create the accounts first by filling in their formulas with 0. Once all of the accounts are created, paste in the formulas for those accounts. This prevents getting "?" for invalid formulas.

To set up loan amortization:

  • Create the following columns in the modeled sheet: 
Column Column Type Code
Loan Type Dimension LoanType
Loan Start Date Date Element StartDate
Initial Loan Balance Number Element Initial
Fixed_Principal_Payment Number Element Fixed_Principal_Payment
Interest Rate Number Element - Percent Interest_Rate
Net Book Value Number Element Input_Balance
Current Date Date Element Current_Date
Loan Maturity Date Date Element MatDate
Loan Products Dimension Element loan_products
Payment Frequency Dimension Element Payment_Frequency
Next Repricing Date Date Element RepriceDate
Next Pay Date Date Element NextPayDate
Re-Pricing Frequency Dimension Element Re-Pricing Frequency

Notes:

  • Loan Type dimension should include different loan categories, Fixed, Variable, ARM
  • Payment Frequency Dimension should have values of 12, 3, 6, 1,
  • Repricing Frequency Dimension should have values of 0, 12, 1, 6, 3
  • Create the following accounts with the attached formulas as calculated accounts:
Account Name Account Code  
Beginning Balance Beginning_Balance  

Formula

IF(ROW.Input_Balance>0 AND VersionMonth(this)=VersionMonth(ROW.Current_Date),ROW.Input_Balance,
IF((ROW.Initial_Loan_Balance-ROW.Initial_Loan_Balance[time=this-1])>0 AND ROW.Input_Balance=0,ROW.Initial_Loan_Balance,
IF(ROW.Ending_Balance[time=this-1]>0,ROW.Ending_Balance[time=this-1],0)))

Interest Payment Interest_Payment  

Formula

(ROW.Beginning_Balance)*DIV(ROW.Interest_Rate,
IF(VersionMonth(this)=VersionMonth(ROW.MatDate),ROW.Last_Interest_Periods,
IF(ROW.Payment_Date=1,ROW.Interest_periods,0)))   

Total Payment Total_Payment

Formula

IF(ROW.Principal_payment_Input>0,ROW.Interest_Payment+ROW.Principal_payment_Input,
IF(ROW.Principal_payment_Input=0 AND ROW.Amort_month=0, ROW.Interest_Payment,
IF(ROW.Payment_Date=1 AND ROW.Amort_month>0 AND  ROW.Amort_month<((ROW.Months)+1),
IF(ROW.Beginning_Balance<(ROW.Initial_Loan_Balance*
DIV(DIV(ROW.Interest_Rate,ROW.Interest_periods),1-power((1+DIV(ROW.Interest_Rate,12)),
(ROW.Months*-1)))),
ROW.Beginning_Balance+(ROW.Beginning_Balance*
DIV(ROW.Interest_Rate,IF(VersionMonth(this)=VersionMonth(ROW.MatDate),ROW.Last_Interest_Periods,
IF(ROW.Payment_Date=1,ROW.Interest_periods,0)))),ROW.Initial_Loan_Balance*
DIV(DIV(ROW.Interest_Rate,ROW.Interest_periods),1-power((1+DIV(ROW.Interest_Rate,12)),(ROW.Months*-1)))),0)))

Ending Balance Ending_Balance  

Formula

ROW.Beginning_Balance-ROW.Principal_Payment

Amort Month Amort_Month  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.StartDate),VersionMonth(this)-VersionMonth(ROW.StartDate)+1,0)

Months Months  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.StartDate) AND VersionMonth(this)<=VersionMonth(ROW.MatDate),VersionMonth(ROW.MatDate)-VersionMonth(ROW.StartDate)+1,0)

Principal Payment Input Principal_Payment_Input  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.StartDate) AND VersionMonth(this)<=VersionMonth(ROW.MatDate),ROW.Fixed_Principal_Payment,0)

Initial Loan Balance Initial_Loan_Balance  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.StartDate) AND VersionMonth(this)<=VersionMonth(ROW.MatDate),ROW.Initial,0)

Interest Rate Interest_Rate  

Formula

IF(VersionMonth(this)=VersionMonth(ROW.Current_Date),ROW.Rate,
IF(ROW.Reprice_Date=1,Row.Rate,ROW.Interest_Rate[time=this-1]))

Payments Payments  

Formula

IF(ROW.Payment_Frequency="1" AND VersionMonth(this)>VersionMonth(ROW.StartDate) AND month(this)=month(ROW.StartDate),ROW.Total_Payment[time=this:this-11],
IF(ROW.Payment_Frequency="3",ROW.Total_Payment[time=this:this-2],
IF(ROW.Payment_Frequency="6" AND VersionMonth(this)=VersionMonth(ROW.StartDate)+6,ROW.Total_Payment[time=this:this-6],
IF(ROW.Payment_Frequency="12" AND VersionMonth(this)>=VersionMonth(ROW.StartDate),ROW.Total_Payment,0))))

Payment Date Payment_Date  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.Current_Date) AND
VersionMonth(this)<=VersionMonth(ROW.MatDate),
IF(ROUND(DIV(VersionMonth(this)-VersionMonth(ROW.NextPayDate),
ROW.Payment_interval))=DIV(VersionMonth(this)-VersionMonth(ROW.NextPayDate),ROW.Payment_interval),1,
IF(VersionMonth(this)=VersionMonth(ROW.MatDate),1,0)),0)

Last Interest Periods Last_Interest_Periods  

Formula

IFF(VersionMonth(this)=VersionMonth(ROW.MatDate),
IF(ROW.Payment_Date[time=this-1]=1,1,
IFF(ROW.Payment_Date[time=this-1:this-2]=1,2,
IFF(ROW.Payment_Date[time=this-1:this-3]=1,3,
IFF(ROW.Payment_Date[time=this-1:this-4]=1,4,
IFF(ROW.Payment_Date[time=this-1:this-5]=1,5,
IFF(ROW.Payment_Date[time=this-1:this-6]=1,6,
IF(ROW.Payment_Date[time=this-1:this-7]=1,7,
IFF(ROW.Payment_Date[time=this-1:this-8]=1,8,
IFF(ROW.Payment_Date[time=this-1:this-9]=1,9,
IFF(ROW.Payment_Date[time=this-1:this-10]=1,10,
IFF(ROW.Payment_Date[time=this-1:this-11]=1,11,0))))))))))),0)

Reprice Date Reprice_Date  

Formula

IF(VersionMonth(this)>=VersionMonth(ROW.Current_Date) AND VersionMonth(this)<=VersionMonth(ROW.MatDate),
IF(ROUND(DIV(VersionMonth(this)-VersionMonth(ROW.RepriceDate),ROW.Reprice_Interval))
=DIV(VersionMonth(this)-VersionMonth(ROW.RepriceDate),ROW.Reprice_Interval),1,
IF(VersionMonth(this)=VersionMonth(ROW.RepriceDate),1,0)),0)

  • Add the following lookup tables:
Column of Lookup Location Name Type Purpose
Loan Products Rate % Interest Rates over time by Product
Payment Frequency Payment_Interval Number Frequency of payments by month
Payment Frequency Interest_Periods Number Interest periods included in each payment
Re-Pricing Frequency Reprice_Interval Number How frequent re-pricing occurs

 

  • Was this article helpful?