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.
No comments:
Post a Comment