How to Create Reverse Mortgage Calculator in Excel

Get FREE Advanced Excel Exercises with Solutions!

Senior citizens in many countries can utilize their home’s equity to obtain cash. This process is known as the reverse mortgage. In this article, we will see the steps to make a reverse mortgage calculator in Excel. The following gives an overview of this article.

Overview of the reverse mortgage calculator in Excel


Download Practice Workbook

You can download the Excel file from the link below.


Steps to Make Reverse Mortgage Calculator in Excel

There will be four steps to create the reverse mortgage calculator in Excel. Firstly, we will create the required fields. Secondly, we will enter the known values. Thirdly, we will create the amortization schedule for the reverse mortgage. Lastly, we will insert a chart to visualize the process.


Step 1: Create Required Fields

We will create the required fields for the reverse mortgage calculator in Excel in this section. Here, the green fill indicates the cell values will be specified by the users. We will insert the formulas in the other cells.

  • Firstly, 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

Type the required fields for the reverse mortgage calculator

Read More: Mortgage payoff calculator with extra principal payment (Free Template)


Step 2: Enter Known Values

In this step, we will input the known values in the green color cells. After that, we will use simple formulas to calculate five values.

  • To begin with, type the known values in the format.
  • After that, use this formula in cell C11 to find the total initial mortgage.

=C8+C9*C10*12

Formula for finding the value of total initial mortgage

  • Then, insert this formula to find the monthly interest rate in cell C19.

=C13/12

  • Afterward, use this formula in cell C20 to return the monthly general inflation.

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

  • Then, type the following formula which will calculate the monthly house inflation in cell C21.

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

  • Lastly, use this formula in cell C22 to find the opening loan after including the establishment fees.

=C8+C15

GIF showing four formulas

  • Notice that the value of cell C18 is missing, we will find this value after completing the third step.

Read More: How to Create Fixed Rate Mortgage Calculator in Excel


Step 3: Create Amortization Schedule

In this section, we will use the IF, SUM, INT, and COUNTIF functions to create the amortization schedule for the reverse mortgage.

  • Firstly, create the following headers-
    • Month
    • Year in Fraction
    • Value of House End of Month
    • Mortgage at Beginning of Month

First part of the amortization schedule for reverse mortgage

  • Secondly, create these additional headers-
    • Monthly Income
    • Accrued Interest
    • Monthly Fee
    • Mortgage Debt at End of Month
    • Equity Remaining
    • Value of Home

Second part of the amortization schedule for the reverse mortgage

  • Thirdly, type in 1 as the first value of the month column, then increase it by 1. Our last value is 777, you should change it as per your requirement.
  • After that, type this formula to convert the month values into the year values.

=B28/12

Finding year fraction

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

Formula to return the value of the house at month end

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

=C22

  • Afterward, type this formula in cell E29 to find the mortgage value at the end of the second month. Then, use the Fill Handle to apply this formula to the rest of the cells.

=I28

Formula to find the mortgage at the beginning

  • Then, use this formula to find the monthly income.

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

Formula to find the monthly income

  • After that, 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

  • Afterward, this formula will return the mortgage debt at the end of the month.

=SUM(E28:H28)

Formula to find the mortgage debt at end of the month

  • Then, use this formula to find the remaining equity.

=D28-I28

Formula to find the equity remaining

  • Lastly, insert this formula to find the value of the home.

=I28+J28

Formula to find the value of the home

  • So, the amortization schedule of the reverse mortgage will look like this.

Amortization schedule of the reverse mortgage

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

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

Formula to find the year when the home equity is zero

Read More: Amortization Schedule with Irregular Payments in Excel (3 Cases)


Step 4: Plot Key Values

In the last step, we will create the Scatter plot to visualize the mortgage debt, equity remaining in the home, and value of the home with respect to time (in years).

  • To begin with, from the Insert tab, select Scatter with Smooth Lines.

Scatter with Smooth Lines chart

  • After that, select the blank chart, and from the Chart Design tab, press on “Select Data”.

Chart Design tab

  • Then, select Add from the Select Data Source dialog box.

The Select Data Source dialog box

  • Then, 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.
  • After that, press OK.

Edit Series dialog box

  • Then, a curved line will appear in the chart.

Initial scatter plot

  • Similarly, repeat the above process for the “Equity Remaining” and “Value of Home” columns.
  • So, from the chart, we can see that after 27 years, the equity becomes zero.

Final scatter plot with smooth lines showing reverse mortgage

Read More: Mortgage Calculator with Extra Payments and Lump Sum in Excel


Conclusion

We have shown you four steps for the reverse mortgage calculator in Excel. Moreover, there is a practice section in the Excel file. You can use that to follow along with this article. Please leave a comment below if you have any questions or concerns about these techniques. However, remember that our website implements comment moderation. Therefore, your comments may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site, ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo