Microsoft Office Tutorials and References
In Depth Information
12. In cell F32, determine the shipping charge by entering an IF function that tests
whether cell C15 equals “standard”. If it does, return the value in cell F9; otherwise,
return the value in cell F10.
13. In cell G32, display the value of cell C15.
14. In cell F34, calculate the total of the after discount value, the sales tax, and the
15. Reduce the quantity of Mountain Rockets boxes from 2 to , and then verify that the 1
discount is changed to 0 for the order.
16. Change the shipping option from overnight to standard , and then verify that the
shipping fee is changed to the fee for standard shipping.
17. Save and close the workbook, and then submit the ﬁ nished workbook to your
instructor, either in printed or electronic form, as requested.
Case Problem 3
to use relative
the PMT function
to create a
Data File needed for this Case Problem: Loan.xlsx
Eason Financial Services Jesse Buchmann is a ﬁ nance ofﬁ cer at Eason Financial
Services in Meridian, Idaho. She works with people who are looking for home
mortgages. Most clients want mortgages they can afford, and affordability is determined by
the size of the monthly payment. The monthly payment is determined by the interest
rate, the total number of payments, and the size of the home loan. Jesse can’t change
the interest rate, but homebuyers can reduce their monthly payments by increasing the
number of years to repay the loan. Jesse wants to give her clients a grid that displays
combinations of loan amounts and payment periods so that they can select a loan that
best meets their needs and budget. Jesse already entered much of the layout and
formatting for the worksheet containing the loan payment grid. You will enter the PMT function.
Complete the following:
1. Open the Loan workbook located in the Excel3\Case3 folder included with your
Data Files, and then save the workbook as Loan Grid .
2. In the Documentation sheet, enter your name and the date.
3. In the Loan Calculation worksheet, in cell E3, enter a monthly payment of $1,750 .
4. In cell E5, enter the annual interest rate of 5.75% . In cell E6, enter to indicate 12
that the interest payment is compounded 12 times a year, or monthly.
5. In the range C10:C20, use AutoFill to enter the currency values $250,000 through
$350,000 in increments of $10,000. In the range D9:H9, use AutoFill to enter the
year values through in increments of 5 years. 35
6. In cell D10, use the PMT function to calculate the monthly payment required to
repay a $250,000 loan in years at 5.75% interest compounded monthly. Use 15
absolute references to cells E5 and E6 to enter the annual interest rate and number
of payments per year. Use the mixed references D$9 and $C10 to cells D9 and C10,
respectively, to reference the number of years to repay the loan and the loan amount.
Place a minus sign before the PMT function so that the value returned by the
function is positive rather than negative.
7. Using AutoFill, copy the formula in cell D10 into the range E10:H10, and then copy
that range of formulas into the range D11:H20.
8. Conditionally format the range D10:H20 to highlight all of the values in the range
that are less than the value in cell E3 in a dark green font on a green ﬁ ll.
9. Add a second conditional format to the range D10:H20 to highlight all of the values
in the range that are greater than the value in cell E3 in a dark red font on a red ﬁ ll.