# 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