In this project, you will complete a series of budget worksheets, including a home loan analysis, rental comparison, and  savings and retirement planning. The fair market rent information in the Final FY2017 Virginia FMR worksheet was  copied from the U.S. Department of Housing and Urban Development Web site at http://www.huduser.org.  Skills needed to complete this project:  • Calculating the Number of Payments with NPER  • Rounding with Functions  • Finding Data with MATCH and INDEX  • Calculating Future Value with the FV Function  • Using PV to Calculate Present Value when Payments Are Constant  • Using NPV to Calculate Present Value when Payments Are Variable  1. Open the start file EX2016-ChallengeYourself-6-4. The file will be renamed automatically  to include your name. Change the project file name if directed to do so by your instructor, and save it.  2. If the workbook opens in Protected View, enable editing so you can make changes to the workbook.  3. On the Loan Terms worksheet, calculate how quickly you could pay off the loan if you increased the  monthly payment.  a. In cell D11, use NPER to calculate the number of months to pay off the loan using the new monthly  loan payment amount in cell D10. Remember to use a negative value for the Pmt argument and be  careful to reference the loan amount, not the home price. You can omit both the Fv and Type  arguments—home loans are usually set up so the payment is applied at the end of each period.  b. In cell D12, calculate the approximate number of years to pay off the loan by dividing the value in  cell D11 by 12 and then rounding to zero decimal places.  4. Look up the equivalent fair market rents in the same area. This data can be found in the Final FY2017  Virginia FMR worksheet.  a. In cell D16 on the Loan Terms sheet, enter a nested formula using MATCH and INDEX to find the  fair market rent for a one bedroom rental unit in the county listed in cell D15. We’ve already defined  two named ranges for you to use. FMR_2017 is the named range to use for the INDEX function  Array argument. This range includes all the data in cells A3:H137 in the Final FY2017 Virginia FMR  worksheet. The one bedroom rent data is in column 4. The second named range, Counties, can be  used as the MATCH Lookup_array argument. This range is the column of county names, cells  G3:G137, in the Final FY2017 Virginia FMR worksheet. Require an exact match for the MATCH  function.  b. In cell D17 on the Loan Terms sheet, enter a nested formula using MATCH and INDEX to find the  fair market rent for a two bedroom rental unit in the county listed in cell D15. You can use the same  formula as in D16, but substitute column 5 for the INDEX Column_num argument.  1 | Page Challenge Yourself 6.4 Last Updated 11/21/17                                                             A Skills Approach: Excel 2016 Chapter 6: Exploring Advanced Functions  Step 3  Grade my  Project  Step 2  Upload &  Save  5. Now that you’ve calculated mortgage and rental options, let’s look at savings. Go to the Savings  worksheet.  a. You want to save at least $10,000 for a down payment on a new car. In cell B6, enter a formula to  calculate how much you will have saved by putting away $400 per month for 24 months at a 1.75%  annual interest rate. Use the appropriate cell references. Remember to use a negative value for the  Pmt argument. There is no money in the account yet and payments are applied at the end of every  month, so omit both the Pv and Type arguments. (Hint: Use the FV function.)  b. You also want to save for retirement. In cell B12, enter a formula to calculate how much you will have saved  by putting away that same $400 per month for 25 years at 4%. Use the appropriate cell references.  Remember to use a negative value for the Pmt argument and to adjust the Rate and Nper arguments to reflect  monthly payments. There is no money in the account yet and payments are applied at the end of every  month, so omit both the Pv and Type arguments. (Hint: Use the FV function.)  c. Once you’ve saved 25 years for retirement, how much can you draw from the retirement account  over the next 30 years? In cell B14, enter a formula to calculate how much you can pay yourself from  the retirement account every month. Assume that the account will continue to earn the same interest  rate. At the end of the 30 years, the account balance will be zero. Payments to you will be made at  the beginning of every month. Use the appropriate cell references. (Hint: Use the PMT function to  calculate the payments to yourself. Enter a negative sign before the formula so the result appears as a  positive number.)  6. You have two options for additional guaranteed monthly payments in retirement (annuities). Go to the  Annuities worksheet to calculate the value of each of these options.  a. The first annuity costs $20,000 and pays you $200 per month for 120 months (10 years). In cell B5,  enter a formula to calculate the present value of this investment. Remember to express the Pmt  argument as a negative number and set the Type argument to 1 as the payment will be made at the  beginning of each month. (Hint: Use the PV function.)  b. The second annuity also costs $20,000 but it pays a variable annual payment. In cell B13, enter a  formula to calculate the present value of this investment using the annual interest rate in cell B8 and  the payment schedule in cells B14:B23. (Hint: Use the NPV function.)  7. Save and close the workbook.  8. Upload and save your project file.  9. Submit project for grading.