Excel Reverse Mortgage Calculator Spreadsheet

How-to basics · Updated November 22, 2026

Yes, you can build a reverse mortgage calculator in Excel using the built-in FV (future value) and PMT functions, but a truly useful spreadsheet must also account for loan limits, interest rate adjustments, and the fact that the loan balance grows over time. The core formula is simple: =FV(rate, nper, pmt, [pv], [type]) to project the total loan balance after a number of years, given a fixed interest rate and monthly payments to the borrower. Below is a complete guide to creating and using such a spreadsheet, including the exact formulas, common pitfalls, and how to interpret the results.

What Is a Reverse Mortgage and Why Use Excel?

A reverse mortgage is a loan for homeowners aged 62 or older that allows them to convert part of their home equity into cash without making monthly mortgage payments. The loan is repaid when the borrower sells the home, moves out permanently, or passes away. Unlike a forward mortgage, the loan balance increases over time because interest is added to the principal and payments (if any) are made to the borrower, not to the lender.

Excel is a practical tool for modeling this because the math is essentially a future value calculation. You can quickly see how different interest rates, loan amounts, or payment schedules affect the balance over 10, 20, or 30 years. However, a reverse mortgage is a complex financial product with many rules, so a spreadsheet should be used for education and estimation, not as a substitute for official lender disclosures.

Core Formulas for a Reverse Mortgage Calculator

To build a basic calculator, set up your spreadsheet with these inputs in cells B2:B6 (for example):

  • B2: Initial loan amount (e.g., $200,000)
  • B3: Annual interest rate (e.g., 5.5%)
  • B4: Loan term in years (e.g., 20)
  • B5: Monthly payment to borrower (e.g., $500) – can be 0
  • B6: Number of payments per year (usually 12)

Then use these formulas:

Projected loan balance after the term:
=FV(B3/B6, B4*B6, B5, -B2, 0)
This returns the future value of the loan, assuming the rate is constant and payments are made at the end of each month. For example, with $200,000 initial, 5.5% annual rate, 20 years, and $500 monthly payments, the result is approximately $1,015,000 (the negative sign on B2 represents the loan proceeds received).

Total interest accrued:
=FV(B3/B6, B4*B6, B5, -B2, 0) - B2 - (B5 * B4 * B6)
This subtracts the original principal and all payments made to the borrower, leaving the interest portion.

Monthly payment needed to reach a target balance:
If you want to know how much you could borrow each month to reach a specific balance, use PMT:
=PMT(B3/B6, B4*B6, -B2, target_balance, 0)
For instance, with $200,000 initial, 5.5% rate, 20 years, and a target balance of $500,000, the formula returns about -$1,200 per month (negative indicates a payment to the borrower).

How to Build a Full Spreadsheet Step by Step

  1. Open Excel and create a new workbook. In column A, list labels: “Initial Loan Amount,” “Annual Interest Rate,” “Loan Term (Years),” “Monthly Payment to Borrower,” “Payments per Year.”
  2. Enter the corresponding values in column B. Use a format like 5.5% for the rate (or type 0.055 and format as percent).
  3. In cell B8, enter the formula for the projected balance: =FV(B3/B6, B4*B6, B5, -B2, 0).
  4. In cell B9, enter the total interest formula: =B8 - B2 - (B5 * B4 * B6).
  5. Format cells B8 and B9 as currency to see the results clearly.
  6. Add a data table (optional) to see how the balance grows each year: In column D, list years 1 to 30, and in column E, use =FV($B$3/$B$6, D2*$B$6, $B$5, -$B$2, 0) and drag down.

This layout gives you a clear picture of the loan’s trajectory. You can also add a chart by selecting the year and balance columns and inserting a line chart.

Choosing the Right Interest Rate and Loan Terms

Reverse mortgages typically have adjustable interest rates, tied to an index like the Secured Overnight Financing Rate (SOFR) plus a margin. The initial rate is often lower than a fixed rate but can rise over time. When modeling, use a conservative estimate—maybe 1-2% higher than the current rate—to see a worst-case scenario.

The loan amount also depends on the borrower’s age, the home’s value, and the lending limit set by the Federal Housing Administration (FHA) for Home Equity Conversion Mortgages (HECMs). In 2025, the FHA lending limit is $1,149,825 for most areas, but the actual principal limit is a percentage of that, often 40-60% for younger borrowers. Your spreadsheet should use the actual principal limit you receive from a lender, not the full home value.

Common Problems and Misconceptions

Misconception: The bank owns your home. In reality, you retain the title and ownership; the lender has a lien, similar to a forward mortgage. The loan must be repaid when you leave the home or sell it.

Problem: Forgetting that interest compounds. The balance can grow quickly, especially with a high rate. For example, a $300,000 loan at 6% with no payments will double in about 12 years. Excel’s FV function handles this automatically, but users often underestimate the impact.

Problem: Using the wrong sign for the present value. In the FV formula, the initial loan amount should be negative because it’s money you receive. If you enter it as positive, the result will be negative, which confuses interpretation.

Misconception: You can never owe more than the home’s value. For HECM loans, the FHA insurance guarantees that you won’t owe more than the home’s value if you sell, but the loan balance can exceed the home’s value in the meantime, and that affects your equity.

Comparisons: Reverse Mortgage vs. Home Equity Loan or HELOC

Many seniors compare reverse mortgages to home equity loans or home equity lines of credit (HELOCs). Here’s a quick comparison:

  • Reverse mortgage: No monthly payments required; interest accrues; loan balance grows; must be repaid when you leave the home; proceeds are tax-free (as loan advances).
  • Home equity loan: Lump sum, fixed rate, requires monthly payments; you must have sufficient income to qualify; risk of foreclosure if you default.
  • HELOC: Variable rate line of credit, requires monthly payments (at least interest); you can draw as needed; also requires income qualification.

A reverse mortgage can be beneficial if you have little income but significant home equity and want to stay in your home. However, the upfront costs (origination fees, mortgage insurance premiums) are typically higher than a HELOC. Your Excel model can help compare the total cost over time by including these fees in the initial loan amount.

Costs and Safety Considerations

Reverse mortgages come with significant costs that should be included in any spreadsheet model:

  • Origination fee: Up to $6,000 for HECM loans, depending on the home value.
  • Mortgage insurance premium (MIP): 2% of the home value upfront, plus an annual 0.5% of the loan balance.
  • Third-party fees: Appraisal, title search, recording, and counseling (counseling is mandatory for HECM).

These costs are typically financed into the loan, meaning they increase the initial balance. In your spreadsheet, add these to the initial loan amount. For example, if your principal limit is $200,000 but costs are $10,000, the actual initial balance is $210,000.

Safety note: A reverse mortgage is a loan, not a government benefit. You must continue to pay property taxes, homeowners insurance, and maintain the home. Failure to do so can trigger a default and foreclosure. Always consult with a HUD-approved counselor and review the loan terms with a qualified professional before proceeding. This article is for educational purposes only and does not constitute financial advice.

Frequently Asked Questions

Can I use Excel to calculate the exact amount I can borrow? No, the exact amount depends on FHA rules, your age, and the home value. Excel can estimate, but you need a lender’s official calculation. Use the HECM calculator on the HUD website for a preliminary figure.

What is the best interest rate to use in my model? Use the current rate from a lender plus a margin for safety. Since rates are adjustable, modeling with a 1-2% higher rate gives a realistic worst-case scenario.

How do I account for the 2% upfront mortgage insurance premium? Simply add it to the initial loan amount in cell B2. For example, if the base loan is $200,000 and MIP is $4,000, enter $204,000.

Does the spreadsheet work for a fixed-rate reverse mortgage? Yes, the FV function works the same; just enter the fixed rate and set the term (often 10, 15, or 20 years for fixed-rate HECMs).

What if I don’t take monthly payments, just a lump sum? Set the monthly payment to 0. The FV formula will then show just the growth of the initial balance.

Can I see how much equity I’ll have left? Yes, subtract the projected loan balance from the projected home value. Add a cell for expected home appreciation rate and use =home_value*(1+appreciation_rate)^years to project the home value.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.