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

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
Principal_Payment Number Element 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
Payment Frequency Dimension Element Payment_Frequency
Next Repricing Date Date Element RepriceDate
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

 

  • 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
  • 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)))   

Principal Payment Principal_Payment  

Formula

(ACCT.Amort_Cube.Beginning_Balance)*DIV(ACCT.Amort_Cube.Interest_Rate,12)

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)

 

  • Was this article helpful?