Module 03 — Financial Functions & Amortization

Goal

The time-value-of-money function set, plus a real monthly amortization schedule and a lender yield.

Inputs (illustrative)

$10,000,000 permanent loan, 5.85% fixed, 30-year amortization (360 months), 10-year term (balloon at month 120), monthly payments, 1.00% origination fee.

Formulas & concepts featured

• PMT (level payment), IPMT/PPMT (interest/principal split), CUMIPMT/CUMPRINC, NPER, RATE, NPV/XNPV, IRR/XIRR.
• Sign convention: PMT/IPMT/PPMT return negative (cash out). Negate to show positive in the schedule, and keep signs consistent into XIRR.
• Corkscrew ending balance = beginning − principal; balloon = the ending balance at month 120.
• XIRR over dated lender cash flows (net funding, payments, balloon).

Day-count note

PMT/IPMT/PPMT assume level 30/360 compounding — correct for this fixed perm loan. XIRR discounts on Actual/365. Different bases; both fine here because the schedule is fixed and monthly.

Common mistakes

• Leaving PMT negative and breaking the corkscrew.
• Hardcoding the period in IPMT instead of referencing the month number.
• Using IRR (equal-period) on dated cash flows — use XIRR.

Your assignment

Open 03_FinancialFunctions_ASSIGNMENT.xlsx: build the schedule, split I/P, find the balloon, and compute lender XIRR and MOIC. Self-Check grades the payment, month-120 balance, and XIRR.