Microsoft Office Tutorials and References
In Depth Information
Practice the skills
you learned in
the tutorial using
the same case
Data File needed for the Review Assignments: Timov.xlsx
Sergei and Ava Timov, friends of Diane and Glenn, ask you to create a similar workbook
for their family budget. The Timovs want to purchase a new home. They are
considering two houses with different mortgages. They want the budget worksheet you create to
display the impact of monthly mortgage payments on the couple’s cash ﬂ ow. They also
want to be able to quickly switch between one mortgage plan and another to observe the
impact of each plan on their budget. The couple has already designed the workbook and
entered estimates of their income and expenses for the upcoming year. You need to set
up the formulas. Complete the following:
1. Open the Timov workbook located in the Excel3\Review folder included with your
Data Files, and then save the workbook as Timov Family Budget .
2. In the Documentation sheet, enter your name in cell B3. Use the TODAY function to
display the current date in cell B4.
3. In the Family Budget worksheet, in the range C18:N18, use AutoFill to replace the
month numbers with the abbreviations Jan through Dec .
4. In the range C21:N21, calculate the couple’s total income. In the range C27:N27,
calculate the couple’s total monthly expenses. In the range C28:N28, calculate the
monthly net cash ﬂ ow (equal to the total income minus the total expenses).
5. In cell C7, enter a formula to calculate the sum of Sergei’s monthly income for the
entire year. In cell D7, calculate Sergei’s average monthly income. In cell E7,
calculate Sergei’s maximum monthly income. In cell F7, calculate Sergei’s minimum
6. Complete the Year-End Summary table by ﬁ rst selecting the range C7:F7. Use
AutoFill to copy the formula in the range C7:F7 into the range C7:F16. Use the Auto
Fill Options button to copy only the formulas into the selected range and not both
the formulas and the formats. ( Hint : Because you haven’t yet entered any mortgage
payment values, cell D14 will show the value #DIV/0!, indicating that Excel cannot
calculate the average mortgage payment. You’ll correct that problem shortly.)
7. In the range K2:K7, enter the following loan and loan conditions of the ﬁ rst
a. In cell K2, enter 6.7% as the annual interest rate.
b. In cell K3, enter as the number of payments per year. 12
c. In cell K4, calculate the monthly interest rate.
d. In cell K5, enter as the number of years in the mortgage. 30
e. In cell K6, calculate the total number of months to repay the loan.
f. In cell K7, enter 395,000 as the loan amount.
8. In cell K8, use the PMT function to calculate the monthly payment required to repay
9. Edit the formula in cell K8, adding a minus sign directly before the PMT function to
make the value returned by the formula positive rather than negative.
10. In the range K10:K15, enter the following conditions of the second mortgage plan,
and calculate the monthly interest rate and the total number of months to repay
• The annual interest rate is 6.7% .
• The interest rate is compounded times a year (or monthly). 12
• The mortgage will last years. 20
• The loan amount (or value of the principal) is $300,000 .