Financial
Reverse mortgage illustration: growing balance and projected home equity with FV
Model an initial draw and fixed end-of-month advances at a constant monthly rate, then compare debt with an assumed future property value.
A simplified cash-flow illustration, not a HECM quote
The model starts with an initial draw and adds equal advances at each month end, with no repayments. B5 is an illustrative nominal annual rate divided by twelve, not an effective annual yield or a quoted APR including fees. FV uses payment type 0 explicitly.
This constant-rate calculation excludes separately financed fees, mortgage-insurance charges, changing rates, loan limits, eligibility rules and sale costs. It does not calculate an actual HECM balance or the amount an insurer will pay.
MAX(0,home value minus debt) reports only the positive part of projected equity. It neither enforces a contract nor determines heirs' repayment obligations. The signed difference and the opposite positive shortfall are shown separately. Consult the linked CFPB guidance for HECM repayment circumstances.
| Illustrative metric | Formula | 10 Yrs (+3%/yr Home Growth) | 10 Yrs (-3%/yr Housing Slump) |
|---|---|---|---|
| Compounded Loan Balance | -FV(B5/12, B6*12, B4, B3) | $360,400.92 | $360,400.92 |
| Projected Home Value | B2 * ((1 + B7) ^ B6) | $604,762.37 | $331,840.86 |
| Positive projected equity | MAX(0, HomeVal - LoanBal) | $244,361.45 | $0.00 |
| Projected debt above property value | MAX(0, LoanBal - HomeVal) | $0.00 | $28,560.07 (model shortfall) |
Balance with advances at month end
The horizon must correspond to a whole number of months. A tolerance of 0.00000001 month accommodates floating-point input; the accepted count is rounded to a whole month. Initial draw, advance and rate must be nonnegative. At zero interest, debt is simply draw plus total advances.
=IF(COUNT(B3:B6)<>4,"Invalid inputs",IF(OR(B3<0,B4<0,B5<0,B6<0,ABS(B6*12-ROUND(B6*12,0))>0.00000001),"Invalid inputs",ROUND(-FV(B5 / 12, ROUND(B6 * 12, 0), B4, B3, 0), 2)))Positive part of projected equity
Property growth is an assumed effective annual rate, compounded over the accepted horizon. It can be negative but must exceed -100%. This projection is not an appraisal or a guaranteed sale value.
=IF(COUNT(B2:B7)<>6,"Invalid inputs",IF(OR(B3<0,B4<0,B5<0,B6<0,ABS(B6*12-ROUND(B6*12,0))>0.00000001,B2<=0,B7<=-1),"Invalid inputs",MAX(0, ROUND((B2 * ((1 + B7) ^ (ROUND(B6 * 12, 0) / 12))) - (-FV(B5 / 12, ROUND(B6 * 12, 0), B4, B3, 0)), 2))))Signed property-minus-debt difference
A negative result exposes the modeled shortfall instead of hiding it behind a zero floor.
=IF(COUNT(B2:B7)<>6,"Invalid inputs",IF(OR(B2<=0,B3<0,B4<0,B5<0,B6<0,B7<=-1,ABS(B6*12-ROUND(B6*12,0))>0.00000001),"Invalid inputs",ROUND(B2*((1+B7)^(ROUND(B6*12,0)/12))-(-FV(B5/12,ROUND(B6*12,0),B4,B3,0)),2)))Positive modeled shortfall
This is the opposite positive part of the signed difference, not an insurance claim calculation.
=IF(COUNT(B2:B7)<>6,"Invalid inputs",IF(OR(B3<0,B4<0,B5<0,B6<0,ABS(B6*12-ROUND(B6*12,0))>0.00000001,B2<=0,B7<=-1),"Invalid inputs",MAX(0, ROUND((-FV(B5 / 12, ROUND(B6 * 12, 0), B4, B3, 0)) - (B2 * ((1 + B7) ^ (ROUND(B6 * 12, 0) / 12))), 2))))Try the example
Use the original values or change them to test your understanding.