Loan constants and rate sensitivity in proceeds estimates

A loan constant is annual scheduled principal and interest divided by original principal under a specified payment structure. For a fully amortizing loan, it is generally higher than the note rate because payments repay principal as well as interest. Using the coupon as the constant can overstate debt capacity even when the cash-flow estimate and coverage requirement are correct.

Microsoft’s PMT reference documents level-payment calculations and consistent period units. It is a computational reference, not a lending standard. This guide develops an original sensitivity table using monthly, month-end payments at constant nominal annual rates, with no fees, reserves, irregular first periods, or additional curtailments.

Define the formula and its units

Let r be the nominal annual rate, i = r / 12 the monthly rate, and n the total number of monthly amortization payments. The monthly payment per dollar of principal is i / (1 − (1 + i)^(-n)). Multiplying by twelve gives the annual constant k.

For principal L, annual scheduled debt service is L × k. For annual recognized cash flow C and an assumed minimum coverage d, the cash-flow sizing limit is C / (d × k). Keep percentages as decimals in calculations: 7% is 0.07, not 7.

At a zero interest rate, the formula’s ordinary expression needs a separate limit: monthly payment is principal divided by n, and the annual constant is 12 / n. A zero-rate 30-year amortization therefore has a 3.3333% annual constant, not zero. It still repays principal.

Compare six hypothetical payment structures

Assume recognized cash flow of $500,000 and a coverage requirement of 1.25 times. Permitted annual debt service is $400,000. The following table varies only rate and amortization; figures are rounded for display after calculation with unrounded values.

Longer amortization lowers the scheduled annual payment in this model, supporting more principal at the same cash flow and coverage. It also repays principal more slowly. A larger initial loan or lower payment is not a complete comparison of maturity risk, total financing cost, or flexibility.

Check one row by two routes

At 6% with 30-year amortization, the annual constant is approximately 0.071946063. A $4 million loan requires about $287,784 of annual principal and interest. The spreadsheet cross-check is -PMT(6%/12,360,4000000)*12 under the stated payment convention.

Dividing the $400,000 debt-service budget by the unrounded constant yields approximately $5,559,720 of principal. Multiplying that principal back by the constant returns $400,000. This reverse check catches percentage and unit mistakes without depending on the same displayed rounded number twice.

If someone uses the 6% coupon instead, the result is $6,666,667. That is the interest-only capacity under a constant 6% rate, not the 30-year amortizing capacity. The error is a change of payment structure hidden inside the denominator.

Do not round the constant before sizing

A displayed constant is a summary, not the calculation input. Retain adequate precision internally, calculate principal, and round the final dollar result for communication. Otherwise two spreadsheets using 7.19% and 7.1946063% can disagree even though they intend the same structure.

Record whether the source rate is nominal with monthly division, an effective annual rate requiring conversion, or a contract using a different convention. Daily accrual, actual/360 interest, irregular first periods, floating resets, and payment recasts require their own schedules. A level-payment table should not be represented as an exact contractual payoff calculation for those loans.

Separate amortization from loan maturity

A five-year loan can use 30-year amortization. In that case the 30-year schedule establishes regular payments, while maturity ends the loan earlier with a remaining balance. Do not enter five years as the amortization period simply because the note matures then, unless the contract actually requires full repayment through five years of scheduled payments.

Likewise, an interest-only period followed by amortization needs a dated schedule. A single constant can describe a particular tested phase, but it does not reproduce a changing payment sequence or establish the balloon due at maturity.

Use the sensitivity table in a decision

Apply the table to a confirmed cash-flow definition, then compare its limit with value, cost, loan-size and other constraints. Once another constraint binds, a lower constant may improve cash flow without increasing available principal. If the lender requires a stressed rate, use that rate for its sizing test while preserving the actual rate for the cash budget.

The practical deliverable is a small table with explicit units, assumptions, a reproducible payment check, and the resulting binding constraint. It turns “rates moved” into a measurable payment and capacity change while keeping the limitations of a constant-payment model visible.