Calculate the ratio of the annual mortgage payments to household income that is required to service a mortgage given the prevailing house price and interest rates.

The value in cell "H2" is 0.63. What does this mean?

Instructions:

(1) In cell "H1" write "Mortgage Payments to Income". In cell "H2" use the "=PMT(rate, number of periods, present value)" function in Excel to calculate the annual mortgage payment.

The PMT formula calculates the payment each year in order to pay off the whole loan over a 30 year period. The interest rate needs to be entered as a decimal so we divide by 100. We put a minus in front as PMT produces a negative value because it is a payment. We divide this value by income to determine the proportion of income that is required to pay the mortgage. Use the following formula in cell "H2":

"=-PMT(E2/100,30,B2)/D2"

(2) Drag this formula down to the end of the column. Do this by double clicking on the bottom right of the cell "H2" when the cursor makes a black plus sign.

(3) Plot the resulting series. Highlight your column of mortgage payments to income, go to the "Insert" tab then "Charts" and choose a line chart.