ETC2430 Actuarial Statistics Assignment 2019
Topic: Analysis of Reverse Mortgages
This assignment should be submitted via moodle before 11.59pm on Thursday May 2nd 2019.
A. Background
In this assignment you will use the various theories you have learned in this unit (up to Week 5) to analyse a consumer lending product known as a Reverse Mortgage.
You can read about reverse mortgages on the ASIC MoneySmart website here:
https://www.moneysmart.gov.au/superannuation-and-retirement/income-sources-in- retirement/home-equity-release/reverse-mortgages
It is important that you read this material and understand some of the general issues associated with reverse mortgages. An example of a Reverse Mortgage calculator is available on the ASIC website.
B. Your Tasks
1. Building a Model
Construct a spreadsheet model that analyses a reverse mortgage product.
A retired person is applying for a reverse mortgage, with the equity in their home as the security for the loan. The reverse mortgage provides a “line of credit” for the borrower, where they can borrow up to a given limit over a period of time. The accumulated loan will be repaid to the lender when the home is sold.
This person is going to use the line of credit to provide for their living expenses with an annuity paid monthly into their bank account. The amount of credit will be an annuity, either a level annuity or an index-linked annuity increasing annually with the expected rate of consumer price inflation.
The lender needs to set a maximum value for the line of credit to provide a reasonable confidence that the borrower will not end up in a “negative equity” situation (ie the home value is less than the accumulated loan). For this product, the maximum will be set such that the present value of the annuity should not exceed a certain percent (e.g. 60%) of the initial valuation of the home.
Here are some of the variables that determine the model.
• Current / Initial home valuation
• The number of years the person can expect to live. Try, say, 12 years at first.
• The interest rate charged by the bank for the loan amount owed on the reverse mortgage.
• The annual inflation rate currently and into the future. For simplicity, assume a constant
inflation rate in every time period.
• The annual increase in home valuation.
Look for realistic values of the parameters above, and in your report explain the values and the sources you used to derive them.
Use the model to calculate the maximum possible amount of the monthly annuity (either a level or indexed annuity). You need to analyse both annuity options.
2. Analysis
2.1 Annuity Calculations
You need to calculate the present value of the annuities in two ways:
a. Use the spreadsheet model to calculate the present value of each annuity payment at each time period, and then add these across all payments.
b. Use the theoretical methods developed in the lectures to calculate the present value of the level annuity and the indexed annuity.
Compare the results from the two methods. In your report, comment on any similarities or differences.
2.2 Monitoring debt:equity ratio
You also need to calculate the debt:equity ratio over the annuity period. To do this, you need to project the home value (equity) and cumulative amount owed (debt) each month. (The cumulative amount owed will be the sum of the annuity payment plus interest accrued each month.).
Calculate the debt:equity ratio for each month. Note what happens to the debt: equity ratio over time. Produce a graph that shows this, and discuss it in your report.
2.3 Sensitivity Analysis
Try varying some of the parameters and see what difference it makes to the results you find.
In particular, you are concerned about the possibility of “negative equity”, when the debt: equity ratio exceeds one. This is most likely to occur if home prices increase at a lower rate than loan interest rates.
Note: we have deliberately given you little detail about how to design your model and do this analysis. We want you to have a go, try some things, and then ask for feedback, then keep working on it. You don’t need a “complete” model before you come and ask for help and feedback.
You can come to any of the tutor consultation times, or chat with your tutor or lecturer at your weekly classes.
C. What to submit
Once you have finished building the model and the analysis, we would like you to do the following:
1. Produce a well formatted spreadsheet, with clear explanation of the parameters and meaning of variables and appropriate checks.
2. Write a succinct report outlining model parameters, structure, and results, and implications of your sensitivity analysis. Focus on the risks with this design of the reverse mortgage. Your target audience is an Actuary in a firm looking for feedback on the design of this reverse mortgage product. Maximum 600 words! We will ignore any words past 600 …
Submit:
• Your spreadsheet model (Excel format)
• Your short report (pdf document) using moodle’s Assignment section.
D. Marking guide
Task
Details
Marks
Spreadsheet model
Level & indexed annuity sheets
Present value calculations correct
Parameters, calculations, results well formatted Calculations well explained eg goal seek Appropriate checks
12
Report
Word limit met
Parameters explained and sources in Appendix Model structure described
Results presented in table/ chart
Sensitivity analysis
8
Total
20