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.

## 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

**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`

- 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`

- 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

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

- 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`

- Then, type this formula to find the value of the house at the end of the month. Remember to use
**absolute cell references**. 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`

- 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`

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

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

- 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)`

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

`=D28-I28`

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

`=I28+J28`

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

- 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)`

**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**.

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

- Then, select
**Add**from 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.

- Series name: cell
- After that, press
**OK**.

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

- 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.

**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!