Microsoft Office Tutorials and References

In Depth Information

**PRACTICE EXERCISE—TED AND ALICE'S HOUSE PURCHASE DECISION**

Calculations Section

Your spreadsheet will need formulas to calculate the apartment rent, house payments, and the interest rate

for the mortgage (see Figure C-35). You will use the rent and house payments later in the Income and Cash

Flow Statements for both renting and buying.

FIGURE C-35

Calculations section

Apartment Rent

The 2011 amount is given. Use IF formulas to increase the rent by 10% if

occupancy rates are high, or by 3% if occupancy rates are low.

—

House Payments

This value is the total of the 12 monthly payments made on the mortgage. An

important point to note is that house mortgage interest is always compounded monthly, not

annually as in the thrift shop tutorial. To properly calculate the house payments for the year,

you divide the annual interest rate by 12 to determine the monthly interest. You also have to

multiply a 30-year mortgage by 12 to get 360 payments, and then multiply the PMT formula by

12 to get the total amount for your annual house payments. Also, you will precede the PMT

function with a negative sign to make the payment amount a positive number. Your formula

should look like the following:

=

—

–

PMT(B22/12,360,C5)*12

Interest Rate for House Mortgage

Use the IF formula to enter a 3% interest rate if the bond

money is available, and a 6% interest rate if no bond money is available.

—

Income and Cash Flow Statements Sections

As with the thrift shop tutorial, you want to see the Income and Cash Flow Statements for two scenarios

in

this case, for continuing to rent and for purchasing a house. Each section ends with cash on hand at the end

of 2011. As you can see in Figure C-36, Ted and Alice only have $4,000 in their savings.

—

FIGURE C-36

Income and Cash Flow Statements sections (for both rent and purchase)