Microsoft Office Tutorials and References
In Depth Information
PRACTICE EXERCISE—TED AND ALICE'S HOUSE PURCHASE DECISION
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.
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.
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:
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
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.
Income and Cash Flow Statements sections (for both rent and purchase)