Student Loan Model

The model's behaviour is shaped by inputs on the Assumptions sheet. Loan terms include the original balance, annual interest rate, grace period, and standard repayment term.

• A capitalisation flag determines whether accrued interest during grace is added to principal. The IDR side relies on the discretionary income formula, which subtracts a multiple of the Federal Poverty Level (FPL) from gross income.
• The payment rate, forgiveness year, family size, and FPL inflation rate are all configurable, allowing the model to represent any IDR plan by adjusting these parameters. Income assumptions include the starting salary, annual growth rate, and the marginal tax rate applied to forgiven amounts.

The model builds two parallel annual schedules: one for standard repayment and one for IDR. In the standard schedule, the annual payment is computed once at grace-end using the PMT function and remains fixed for the entire term.

• Each year, interest is calculated on the opening balance, and principal is reduced by the payment amount. The IDR schedule uses a separate income projection to determine discretionary income and the uncapped IDR payment, which is then capped at the standard payment.
• Interest accrues on the opening balance; payments cover interest first, and any remainder reduces principal. When payments are less than interest, negative amortisation occurs and the balance grows.

No circular references exist because IDR payments depend only on income, not on the loan balance.

The Summary sheet presents a side-by-side comparison of total payments, interest paid, and final balances. For standard repayment, the loan fully amortises, and the final balance is zero.

• For IDR, the model calculates the forgiven balance at the end of the forgiveness year, which is then multiplied by the assumed marginal tax rate to produce the tax bomb. The effective total cost for IDR equals all payments made plus the tax bomb.
• The model also computes a differential and recommends the plan with the lower total cost. Additionally, it derives annual and monthly savings under the recommended plan, providing a clear measure of financial impact.

The effective total cost for IDR = all payments made + the tax bombCopy

This template is designed for individual borrowers or advisors assessing federal student loan repayment options. By adjusting assumptions such as income growth or family size, users can explore how IDR payments evolve and whether they remain affordable relative to income.

• The model also highlights the importance of planning for the tax bomb, a one-time tax liability that can be substantial. The built-in checks ensure that both schedules balance and that payments never exceed the standard amount.
• While the public download is a values-only preview, the underlying structure captures the key relationships needed to compare plans and supports informed decision-making.