Rafiul Haq
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.
- 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!
