How to Create Reverse Mortgage Calculator in Excel (With Easy Steps)

Here’s an overview of the calculator we’ll make.

How to Create a Reverse Mortgage Calculator in Excel (With Easy Steps)

Step 1 – Create the Required Fields

The green fill indicates the cell values will be specified by the users. We will insert the formulas in the other cells.

Create the following fields –
- Value of Home
- Initial Lump Sum Amount
- Installment
- Number of Years for Monthly Payments
- Total Initial Mortgage
- Initial Mortgage (as % of Home Value)
- Interest Rate on Loan
- Monthly Fees
- Establishment Fees
- Assumed Annual Increase in Home Value
- Assumed CPI – CPI means Consumer Price Index.
- Your Equity Falls to Zero After (Years)
- Monthly Interest Rate
- Monthly General Inflation
- Monthly House Inflation
- Opening Loan After Fees

Step 2 – Enter Known Values

Input the known values in the table.
Use this formula in cell C11 to find the total initial mortgage.

=C8+C9*C10*12

Insert this formula to find the monthly interest rate in cell C19.

=C13/12

Use this formula in cell C20 to return the monthly general inflation.

=(1+C17)^(1/12)-1

Insert the following formula which will calculate the monthly house inflation in cell C21.

=(1+C16)^(1/12)-1

Use this formula in cell C22 to find the opening loan after including the establishment fees.

=C8+C15

The value of cell C18 is missing, and we will find this value after completing the third step.

Step 3 – Create the Amortization Schedule

Create the following headers:
- Month
- Year in Fraction
- Value of House End of Month
- Mortgage at the Beginning of the Month

Create these additional headers:
- Monthly Income
- Accrued Interest
- Monthly Fee
- Mortgage Debt at the End of the Month
- Equity Remaining
- Value of Home

Create a series in the month column, starting with 1. We created 777 rows as the example.
Use this formula to convert the month values into the year values in the next column.

=B28/12

$Finding year fraction$

Use this formula to find the value of the house at the end of the month, then use the Fill Handle to autofill the formulas.

=$C$7*(1+$C$21)^B28

Use this formula to input the mortgage value at the beginning of the month in cell E28.

=C22

Insert this formula in cell E29 to find the mortgage value at the end of the second month, then use the Fill Handle to apply the formula to the rest of the cells.

=I28

Use this formula to find the monthly income.

=IF(C28<$C$10,$C$9,0)

Insert this formula to calculate the accrued interest.

=(E28+F28)*$C$19

Use the following formula to find the monthly fee.

=$C$14*(1+$C$20)^B28

This formula will return the mortgage debt at the end of the month.

=SUM(E28:H28)

Use this formula to find the remaining equity.

=D28-I28

Insert this formula to find the value of the home.

=I28+J28

The amortization schedule of the reverse mortgage will look like this.

Insert this formula to calculate the years when the equity of the home falls to zero. The last value is in row 804, so we have used this in the formula.

=INT(COUNTIF(J28:J804,">0")/12)

Step 4 – Plot Key Values

From the Insert tab, select Scatter with Smooth Lines.

Select the blank chart and, from the Chart Design tab, press on Select Data.

Select Add from the Select Data Source dialog box.

Input the following values:
- Series name: cell I27.
- Series X values: Year in Fraction.
- Series Y values: value from the Mortgage Debt at End of Month column. We have included the values up to the cell where the equity remains positive which is row 357. Moreover, “ss!” means the data is in the “ss!” sheet.
Press OK.