Amortization Wraps: Troubleshooting “Interest-Only” periods that transition into P+I.

Wrap-around mortgages and two-step commercial debt structures frequently feature an initial Interest-Only (I-O) period followed by standard Amortizing Principal and Interest (P+I) payments.

Formulas break during this transition when standard financial functions (such as PMT or PPMT) maintain the original full-term amortization length instead of adjusting for the remaining elapsed periods. This leads to under-amortization, leaving an unamortized balloon balance at maturity.

Quick Answer

Recalculate the PMT function at the transition point using the remaining loan term and the active balance:
=IF(Month <= IO_Months, Balance * Monthly_Rate, PMT(Monthly_Rate, Total_Months - IO_Months, -IO_Ending_Balance))
This prevents payment shortfalls and ensures the loan balance amortizes cleanly to zero over the remaining term.

---

The Cause of Amortization Wrap Breaks

A standard wrap structure might involve a 30-year amortization schedule with a 5-year interest-only period. Analysts often apply a single PMT formula across all 360 periods:

=PMT(Rate / 12, 360, -Original_Loan_Amount)

This creates three critical model errors:

1. Term Miscalculation: If payments are interest-only for years 1 through 5, the principal balance does not decrease. At month 61, the borrower only has 25 years (300 months) remaining to pay off the balance. Using 360 months in the PMT formula recalculates a payment that requires 35 years total, leaving a massive unpaid balance at Month 360.
2. Negative Amortization in Dynamic Wraps: If the underlying debt wraps an existing senior note with a different amortization schedule, calculating payments on combined terms without dynamic balance triggers produces negative principal payments.
3. PPMT and IPMT Period Offset: Using =PPMT(Rate, Period, Total_Periods, Loan) causes #NUM! errors if Period exceeds Total_Periods, or applies the wrong interest/principal split because the first 60 months had no principal reduction.

---

The Correct Amortization Engine

To build a reliable 2-step amortization schedule, split the calculation into dynamic columns using explicit logical testing.

Assuming:

• Cell B1: Loan Amount ($1,000,000)
• Cell B2: Annual Interest Rate (0.06 for 6%)
• Cell B3: Total Term in Months (360)
• Cell B4: Interest-Only Period in Months (60)
• Column A (Rows 10 to 369): Month Index (1 to 360)

   |         A         |         B         |         C         |         D         |         E         |
---|-------------------|-------------------|-------------------|-------------------|-------------------|
 9 | Month             | Beginning Balance | Total Payment     | Interest Paid     | Principal Paid    |
10 | 1                 | =B1               | =D10+E10          | =B10*($B$2/12)    | =IF(A10<=$B$4...  |

Step 1: Principal Repayment Column (Cell E10)

During the I-O window, principal payment must evaluate to zero. Once past the I-O window, principal is the difference between total payment and interest.

In cell E10, enter:
=IF(A10 <= $B$4, 0, C10 - D10)

Step 2: Interest Payment Column (Cell D10)

Interest is calculated strictly on the opening balance of the period:
=B10 * ($B$2 / 12)

Step 3: Dynamic Total Payment Column (Cell C10)

This is where the calculation commonly breaks. The payment formula must dynamically switch from simple interest to a recalculated amortizing payment at month 61:

=IF(A10 <= $B$4, D10, PMT($B$2 / 12, $B$3 - $B$4, -$B$1))

• During I-O (Months 1–60): Total payment equals interest payment (D10).
• During Amortization (Months 61–360): The nper argument is explicitly passed as $B$3 - $B$4 (360 – 60 = 300 months). The loan amortizes to precisely $0.00 at month 360.

Step 4: Ending Balance (Cell F10) and Next Beginning Balance (Cell B11)

• Ending Balance (F10): =B10 - E10
• Beginning Balance for Month 2 (B11): =F10

---

Diagnosing Remaining Balance Errors at Maturity

If your amortization schedule reaches Month 360 and shows a non-zero balance:

Symptom	Root Cause	Solution
Balance is positive at Month 360	nper in PMT was set to total term (360) instead of remaining term (300).	Update the payment formula to calculate over $B$3 - $B$4.
Balance jumps to negative before Month 360	The interest-only principal cells are returning values other than 0.	Verify your logical condition uses <= rather than < on the transition month.
P&I payment changes every row after Month 60	The PMT function references dynamic Beginning Balance rather than the locked balance at Month 60.	Lock the PV argument to the balance at month 60, or reference initial principal if no principal was paid down during the I-O window.

For models featuring refinancing checkpoints, variable-rate wraps, or irregular balloon payoffs, see Refinancing Sensitivity: Handling #NUM! in PMT functions with balloon payments. to resolve convergence errors in debt service calculations.

---

Diagnostic Next Steps

• If dealing with wrap structures that have underlying senior debt: Ensure the wrap lender’s net cash flow accounts for the senior note’s amortizing schedule. If the senior debt amortizes while the wrap is interest-only, internal cash yields swing unexpectedly.
• If debt service coverage triggers circular errors: When I-O transitions increase annual debt service, models that dynamically calculate reserve accounts often produce circular references. Check Debt Service Coverage Ratio (DSCR): Fixing #DIV/0! when debt service is zero in “All Cash” scenarios. to isolate debt metrics from reserve funding loops.