The writer is very fast, professional and responded to the review request fast also. Thank you.
Book/movie review
22/08/2022
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.
The writer is very fast, professional and responded to the review request fast also. Thank you.
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more