Answered step by step

Verified Expert Solution

## Question

1 Approved Answer

# please help with just the formulas i need for each section! thank you. What other information is needed?? This is everything.. 3. On the Loon

##
please help with just the formulas i need for each section! thank you. What other information is needed?? This is everything.. 3. On the Loon 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. These data can be found in the Finol FY2020 Virginia FMR worksheet. a. In cell D16, 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 2020 is the named range to use for the INDEX function Array argument. This range includes all the data in cells A3:H135 in the Finol FY2020 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 A3-A135, in the Final FY2020 Virginia FMR worksheet. Requite an exact match for the MATCH function, b. In coll D17. 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. 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 86 . enter a formula to caiculate how much you will have saved by putting away 5500 per month for 24 months at a 1.5% 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 calcutate bow moch you will have saved by putting away that same 5500 per month for 25 years at 40 . Use the appropriate cell references. Remember to use a nogative value for the Pme argument and to adjuat the Fote and Nper arguments to reffect monthly payments. There is no money in the account yet and payments are applied at the end of ewery month. so ormit both the Pvand Type arguments (Hint Use the FV functiond Once yourve 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 yoursel from the retirement account every month. Assume that the accoubt will continue to earn the same interest rate. At the end of the 30 years. the account balance wilt 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 resuit appears as a positive mumber) 6. You have two options for additional guaranteed monthly payiments in reb iement (annuities). Go to the Anmuities watksheet to cakcutafe the value of each of thest options A. The fint annary costi $20,000 and pays you $200 per month for 120 montho (10 yesn). In cell B5, entei a formula to caculate the preient value of this inveitment. Remember to expess the fnif argumatit at a negative number and set the Jypt argument to 1 as the phment wil be made at the beganing of nash monthis: (Hithe Use thin PV hanction) Annuities Options 12 Annuity 1 3 Annuity purchase price 4 Total annuity payments 15 value 6 Number of monthly payments over 10 years 7. Monthly annuity (incoming) Annual interest rate $20,000.00$24,000.00120$200.001.50% Annuity 2 11 Annulty purchase price $20,000,00 $24,000.00 12 Total annuity payments 13 Value 14 year 1 15 year 2 16 year 3 17 Year 4 18 year 5 19 year 6 20 Year? 7 21 year 8 22 year 9 $1,500.00 $1,250.00 $1,250.00 $1,250.00 $5,000.00 $1,250.00 $5,000.00 $1,250.00 $1,250.00 $5,000.00 Year 10 Iaakbiabennett-Ex2019-ChaliengeYourse Retirement Account Monthly contribution Years until retirement 11 Average annual return How much will 1 have at retirement? \begin{tabular}{rr|} \hline & $500.00 \\ \hline & 25 \\ \hline & 4.00% \\ \hline \end{tabular} Over 30 years, how much will that pay me per month? Other Financing Options Rental Options \begin{tabular}{|} \hline County & Arlington County \\ \hline One-Bedroom & \\ \hline Two-Bedroom \end{tabular} 3. On the Loon 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. These data can be found in the Finol FY2020 Virginia FMR worksheet. a. In cell D16, 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 2020 is the named range to use for the INDEX function Array argument. This range includes all the data in cells A3:H135 in the Finol FY2020 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 A3-A135, in the Final FY2020 Virginia FMR worksheet. Requite an exact match for the MATCH function, b. In coll D17. 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. 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 86 . enter a formula to caiculate how much you will have saved by putting away 5500 per month for 24 months at a 1.5% 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 calcutate bow moch you will have saved by putting away that same 5500 per month for 25 years at 40 . Use the appropriate cell references. Remember to use a nogative value for the Pme argument and to adjuat the Fote and Nper arguments to reffect monthly payments. There is no money in the account yet and payments are applied at the end of ewery month. so ormit both the Pvand Type arguments (Hint Use the FV functiond Once yourve 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 yoursel from the retirement account every month. Assume that the accoubt will continue to earn the same interest rate. At the end of the 30 years. the account balance wilt 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 resuit appears as a positive mumber) 6. You have two options for additional guaranteed monthly payiments in reb iement (annuities). Go to the Anmuities watksheet to cakcutafe the value of each of thest options A. The fint annary costi $20,000 and pays you $200 per month for 120 montho (10 yesn). In cell B5, entei a formula to caculate the preient value of this inveitment. Remember to expess the fnif argumatit at a negative number and set the Jypt argument to 1 as the phment wil be made at the beganing of nash monthis: (Hithe Use thin PV hanction) Annuities Options 12 Annuity 1 3 Annuity purchase price 4 Total annuity payments 15 value 6 Number of monthly payments over 10 years 7. Monthly annuity (incoming) Annual interest rate $20,000.00$24,000.00120$200.001.50% Annuity 2 11 Annulty purchase price $20,000,00 $24,000.00 12 Total annuity payments 13 Value 14 year 1 15 year 2 16 year 3 17 Year 4 18 year 5 19 year 6 20 Year? 7 21 year 8 22 year 9 $1,500.00 $1,250.00 $1,250.00 $1,250.00 $5,000.00 $1,250.00 $5,000.00 $1,250.00 $1,250.00 $5,000.00 Year 10 Iaakbiabennett-Ex2019-ChaliengeYourse Retirement Account Monthly contribution Years until retirement 11 Average annual return How much will 1 have at retirement? \begin{tabular}{rr|} \hline & $500.00 \\ \hline & 25 \\ \hline & 4.00% \\ \hline \end{tabular} Over 30 years, how much will that pay me per month? Other Financing Options Rental Options \begin{tabular}{|} \hline County & Arlington County \\ \hline One-Bedroom & \\ \hline Two-Bedroom \end{tabular}

please help with just the formulas i need for each section! thank you.

## Step by Step Solution

There are 3 Steps involved in it

### Step: 1

### Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

### Step: 2

### Step: 3

## Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started