## Friday, September 8, 2017

### Returns on Shared Appreciation Mortgages Calculated In Excel

Returns on Shared Appreciation Mortgages Calculated in Excel

Question Eleven:   An investor provides a homebuyer with \$40,000.   The homebuyer uses the funds as part of a down payment on a \$200,000 home. The house appreciates at the rate of 5 percent per year.

In exchange for the \$40,000 the homeowner agrees to return the \$40,000 investment plus 40 percent of the capital gain to the investor.

Create a spreadsheet that calculates the IRR of this investment for different holding periods.   Present results for holding periods of 3, 7, 10, 15 and 20 years.

Analysis:  This problem does not require the use of any financial functions.

Inputs to the problem are in rows 1, 2, 3, 4 and 6.

Capital gain is calculated in row 5.  Formula for capital gain is

(I * (1+HPI)t – I)

Where I is the initial investment, HPI is house price appreciation annual rate,  and t is number of years in holding period.

Payment to investor calculated in row 7 is the sum of the \$40,000 repayment plus the investor’s share of the capital gain, which is assumed to be 40 percent (row 6).

The results:

 House Price 1 \$200,000 \$200,000 \$200,000 \$200,000 \$200,000 % Increase in House Price 2 0.05 0.05 0.05 0.05 0.05 Holding Period 3 3 5 10 15 20 Initial Investment 4 \$40,000 \$40,000 \$40,000 \$40,000 \$40,000 Capital Gain 5 \$31,525 \$55,256 \$125,779 \$215,786 \$330,660 Investor's Share Of Gain 6 0.4 0.4 0.4 0.4 0.4 Payment to Investor 7 \$52,610 \$62,103 \$90,312 \$126,314 \$172,264 IRR 8 9.6% 9.2% 8.5% 8.0% 7.6%

Note the Investor’s return on shared appreciation mortgages declines with the holding period, holding all other parameters constant.

The spread sheet can easily be modified to consider other rates of house price appreciation and other contract terms.

Here is an interesting article on shared appreciation mortgages:

Authors Note:   This post is question 11 in my Excel Finance tutorial.   Go here for the tutorial.