how am i supposed to do the right formulas.
#3 letter d especially
Safari File Edit View History Bookmarks Window Help Instructions... Destan Layout Reference Com A. Font Paragraph Styles Dictate Create and Share Request Adobe PDF Signatures copy from the SA Website PROJECT STEP 1. Hannah Lee would like to purchase a clinic suite in an medical office condominum where she has been working as a physical therapist for the past 12 years. She has been speaking to loan officers at credit unions and banks and will analyze the monthly cost of mortgage rates as well as review the overall impact this purchase would have on her budget. Switch to the Clinic Mortgage worksheet. In cell Ds, create a formula using the PMT function to determine the monthly payments for the anticipated Clinic mortgage, using the defined names Rate, Term_Years, and Loan_Amount as the rate, nper, and pv arguments in the formula a. Put a negative sign before the PMT function to make the formula return a positive value b In the function, Rate should be divided by 12 to calculate the monthly interest rate, and Term_Years should be multiplied by 12 to calculate the total number of monthly payments. Hannah calculated the anticipated total cost of the clinic using the mortgage interest rate she expects to qualify for She now wants to determine how different interest rates could impact the total cost of the clinic Select the range A12:A26 and fill it with a percent series based on the values in range A12 A13. The values are the interest rates that Hannah will analyze in the Varying Interest Rate Schedule 3. Create a single variable data table to determine the impact that the variable interest rates in the range A12 A26) will have on the total cost of the clinic Shelly Command In cell 011, create a formula without using a function that references coll Dhe monthly payments) 27 of 1378 words E O Focus Page 1 of 5 113% g Word File Edit View Insert Format Tools Table Window Help AutoSave Instructions_.. Saved to my Mac Q Home Insert Draw Design Layout References Tell me Share Comments A 0 Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures a. In cell B11, create a formula without using a function that references cell 05 (the monthly payments) b. In cell C11, create a formula without using a function that references cell D6 (the total interest paid on the loan) In cell 011, create a formula without using a function that references cell D7 (the total cost of the mortgage) d Select the range A11026 and create a single-variable data table, using an absolute reference to cell D3 (the mortgage interest rate) as the Column input cell. To help Hannah identify how each rate in her Variable Interest Rate Schedule compares to the interest rate she anticipates on her mortgage, she decides to highlight the matching interest rate in the schedule with a conditional formatting rule Apply a Highlight Cells conditional formatting rule to the range A12:A26 that formats any cell in the range that is equal to the value in cell D3 (using an absolute reference to cell D3) with Yellow Fill with Dark Yellow Text. Hannah now wishes to finalize the Amortization schedule In cell 34, create a formula without using a function that subtracts the value in cell 14 from the value in cell H4 to determine how much of the mortgage principal is being paid off each year. Copy the formula in cell 34 to the range 5:18 In cell 4, create a formula using the IF function to calculate the interest paid on the mortgage (or the difference between the total payments made each year and the total amount of mortgage principal paid each year) The formula should first check if the value in cell H4 (the balance remaining on the loan each year) is greater than o of the value in cell His greater than the formula should return the value in 34 subtracted from the value in cell DS multiplied by 12 Use relative cell reference to cell 4 and an absolute cell reference to cell Din Use 125055-14 as the true argument value in the Formula) Ir the value in colt is not greater than the formule should return value of o Copy the form from all to the range 1 Apply the Accounting number format with two decimal places and as the Non to the ange RKIR Page 2 of 5 1378 words O Focus 113% MacBook Word File Edit View insert Format Tools Table Window Help Autosave DE Instructions... Saved to my Mac a Home Insert Design Layout References Tell me Share Comments A. A. Paste Font Paragraph Styles Dictate Create and Share Request Adobe PDF Signatures gle 9 defined nome Down Payment Taclocrateforme without using a function that references the Hannah decides to add custom.cell baders to the amortiration schedule to make it easier to read Apply custom cell borders with Blue, Accent 5 Darker 50% (9 column. 6 row in the Theme Colors palette) tine coloras described on the next page They Cothman Modul ESAMI D Add an Outline border with a Medium border column, Sow) to the range 21 Add Vertical Line border with a Light botter style (1 column, 7 row) range Add a Top border with a Light borders (1 column Trow to the E 4K d Add Bottom border with Light border style (1 column, 7th row to the ringe GOKIS 10. To make the various elements of the Chic Mortgage worksheets to select and print anna wants to add customes to anges in the worksheet Apply the custom name Mortgage Payment to the A: Apply the custom name interest Rate_Schedule to the range AW 026 Apply the custom nome Amortization Schedule to the range 2121 11. Anames to the cells in the range DS:07 by selecting the rage CD and crimestrom the selection in the values in the left column the denne 12 Hannah wishes to work, so that she intake any changes to the valu. However, he made tone Page 2 of 5 113% 1378 words Focus - g MacBook A Word File Edit View Insert Format Tools Table Window Help AutoSave SUBE. Instructions. Saved to my Mac Home Insert Draw Design Layout References Tell me Share Comments A. A Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe POF Signatures 12. Hannah wishes to protect the worksheet, so that she doesn't make any accidental changes to the values. However, since she made assumptions about the price of the clinic, the down payment, and the mortgage interest rate, she wants to be able to update these values in the protected worksheet. a. Select and unlock the range B5:56. b. Select and unlock cell D3 Protect the Clinic Mortgage worksheet without a password 13. Hannah had previously hidden a worksheet containing data on her other financial obligations. Now she wants to review the data in that worksheet along with the data she just calculated Unhide the Outstanding Loans worksheet 14 Switch to the Outstanding Loans worksheet In cell 09, create a formola without using a function that determines the total interest associated with the loan. First ultiply the value in cell 07 (the number of years) by the value in cell (the monthly payment) and by 12 (to convert the yearly terms to monthly terme, and then subtract the value in cell 35 (the loan amount) Copy the formula without the formatting in cells into the range CHES 15 Hannah wold like to be able to see the remaining balance of her other lansat the end of the current year In cell B12. create a formule using the PV function to determine the outstanding balance of the loans at the end of the current var using the parameters below For the rate parameter, use the value in cell 6 (the yearly interest rate of the mortgaga) divided by 12 for the root subtract the value in cell 011 (the current O Focus Page 3 of 5 1378 words g Tools Table Window Help Word File Edit View Insert Format Instructions... - Saved to my MacQ 5.UBE. AutoSave Insert Home Draw Design Layout References Tell me Share Comme A 0 Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures Home A Shelly Cashmon Excel 2016 | Module 4: SAM Project tb b For the per parameter, subtract the value in cell 011 (the current year of the loan) from the value in cell 07 (the total number of years of the loan), and multiply that by 12 For the pet parameter, use the value in cel BB (the monthly payment), putting a negative sign before this value to make the outcome of the PV function positive Copy the formula without the formatting from cell B12 to the range C12:E12 Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel Follow the directions on the SAM website to submit your completed project 1 Final Figure 1: Clinic Mortgage Worksheet PT Associates Page 4 of 5 1378 words D Focus 113 O Word File Edit View Insert Format Tools Table Window Help AutoSave ORT A SUDE: Instructions_... - Saved to my MacQ Home Insert Draw Design Layout References Tell me Share Com A Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures Paste Shelly Cashman Excel 2016 L Module SAM Proctib Final Figure 2: Outstanding Loans Worksheet PT Associates 3 Loan Type Year borrowed 4 Price 5 Loan Amount 6 Rate 7 Term (Years & Monthly Payment Total interest 10 Total Cont 11 Current Year of loan Loan Balance and of Current year Car Equipment Condominium College 2010 2011 2010 $ 25,000.00 5 70,000.00 $100,000.00 10,000.00 $ 20.000.00 S5,000.00 $ 300,000.00 $ 60,000.00 5.255 6359 4.5.SON 2.SSON 10 30 13 $ 373 375 60.58 1,228345 40142 $ 3.745.543 1949.0 202,202.13 5 12.267. 5214502,202.11 2.267. 12 SA 420.03 20:290275 0.20 $ 11,902 Dom Clint Mortgage Outstanding Lan Page 5 of 5 1378 words Focus UW de layout Formulas Dat Review View Help P Search Shar A Dame Une in forma Manager Create from Section to sam Recently and logical Test & Look & Math More Ud- Tine Heterence Trg Functions Show Formula Parace Dependents by Enor Checking Remove Arrow- con Wale Window Con Fonction 811 PT Associates 3 Date tem 5 Price 6 Down Payment 7 Loan Amount Mortgage Loan Payment Calculator 15 Aug 18 Rate Clinie Term (Years 375.000.00 Monthly Payment 75,000.00 Total interest 300,000.00 Total Cost Year 1 2 5.250X 15 2,41163 134,093.97 434,093.97 5 5 $ AM $ s s 9 10 Rate 11 12 13 14 15 T6 17 Varying Interest Rate Schedule Mootd Payment Total Tral Cost $ 134,093.97 $ 434,093.97 3.000% 3250% 3:500% 3.750% 4.000% 4.250% 4.500 4.750% S.ODOS 5.250% 5.500% 5.7SON 000 6.250 6500% 4 5 6 7 8 9 10 11 12 13 14 15 Amortization Schedule Bestinn Balance Endine balance 300,000.00 286,488 35 286,458.35$ 272.250.02 s 272.250 025 257,245.93 $ 257,245.935 241,434.89 s 241.434.89 $ 224.773.50 $ 224,773.50 $ 207,216.03 5 207,216.035 188,714.29 5 188.71425 169.217.49 s 169,217.495 148,672.11 5 148,672.11 $ 127,021.76 s 127.021.26 $ 104,20702 $ 104,207.02 80,165 26 $ 80,165.26 5 54,830.48 5 54830 48 5 28.133.16 $ 28.133.16 S Subtotal $ Down Payment Total Cost 19 20 21 22 23 24 25 26 27 28 29 Documentation Clinic Mortgage Type here to search O RI E 9 Home Safari File Edit View History Bookmarks Window Help Instructions... Destan Layout Reference Com A. Font Paragraph Styles Dictate Create and Share Request Adobe PDF Signatures copy from the SA Website PROJECT STEP 1. Hannah Lee would like to purchase a clinic suite in an medical office condominum where she has been working as a physical therapist for the past 12 years. She has been speaking to loan officers at credit unions and banks and will analyze the monthly cost of mortgage rates as well as review the overall impact this purchase would have on her budget. Switch to the Clinic Mortgage worksheet. In cell Ds, create a formula using the PMT function to determine the monthly payments for the anticipated Clinic mortgage, using the defined names Rate, Term_Years, and Loan_Amount as the rate, nper, and pv arguments in the formula a. Put a negative sign before the PMT function to make the formula return a positive value b In the function, Rate should be divided by 12 to calculate the monthly interest rate, and Term_Years should be multiplied by 12 to calculate the total number of monthly payments. Hannah calculated the anticipated total cost of the clinic using the mortgage interest rate she expects to qualify for She now wants to determine how different interest rates could impact the total cost of the clinic Select the range A12:A26 and fill it with a percent series based on the values in range A12 A13. The values are the interest rates that Hannah will analyze in the Varying Interest Rate Schedule 3. Create a single variable data table to determine the impact that the variable interest rates in the range A12 A26) will have on the total cost of the clinic Shelly Command In cell 011, create a formula without using a function that references coll Dhe monthly payments) 27 of 1378 words E O Focus Page 1 of 5 113% g Word File Edit View Insert Format Tools Table Window Help AutoSave Instructions_.. Saved to my Mac Q Home Insert Draw Design Layout References Tell me Share Comments A 0 Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures a. In cell B11, create a formula without using a function that references cell 05 (the monthly payments) b. In cell C11, create a formula without using a function that references cell D6 (the total interest paid on the loan) In cell 011, create a formula without using a function that references cell D7 (the total cost of the mortgage) d Select the range A11026 and create a single-variable data table, using an absolute reference to cell D3 (the mortgage interest rate) as the Column input cell. To help Hannah identify how each rate in her Variable Interest Rate Schedule compares to the interest rate she anticipates on her mortgage, she decides to highlight the matching interest rate in the schedule with a conditional formatting rule Apply a Highlight Cells conditional formatting rule to the range A12:A26 that formats any cell in the range that is equal to the value in cell D3 (using an absolute reference to cell D3) with Yellow Fill with Dark Yellow Text. Hannah now wishes to finalize the Amortization schedule In cell 34, create a formula without using a function that subtracts the value in cell 14 from the value in cell H4 to determine how much of the mortgage principal is being paid off each year. Copy the formula in cell 34 to the range 5:18 In cell 4, create a formula using the IF function to calculate the interest paid on the mortgage (or the difference between the total payments made each year and the total amount of mortgage principal paid each year) The formula should first check if the value in cell H4 (the balance remaining on the loan each year) is greater than o of the value in cell His greater than the formula should return the value in 34 subtracted from the value in cell DS multiplied by 12 Use relative cell reference to cell 4 and an absolute cell reference to cell Din Use 125055-14 as the true argument value in the Formula) Ir the value in colt is not greater than the formule should return value of o Copy the form from all to the range 1 Apply the Accounting number format with two decimal places and as the Non to the ange RKIR Page 2 of 5 1378 words O Focus 113% MacBook Word File Edit View insert Format Tools Table Window Help Autosave DE Instructions... Saved to my Mac a Home Insert Design Layout References Tell me Share Comments A. A. Paste Font Paragraph Styles Dictate Create and Share Request Adobe PDF Signatures gle 9 defined nome Down Payment Taclocrateforme without using a function that references the Hannah decides to add custom.cell baders to the amortiration schedule to make it easier to read Apply custom cell borders with Blue, Accent 5 Darker 50% (9 column. 6 row in the Theme Colors palette) tine coloras described on the next page They Cothman Modul ESAMI D Add an Outline border with a Medium border column, Sow) to the range 21 Add Vertical Line border with a Light botter style (1 column, 7 row) range Add a Top border with a Light borders (1 column Trow to the E 4K d Add Bottom border with Light border style (1 column, 7th row to the ringe GOKIS 10. To make the various elements of the Chic Mortgage worksheets to select and print anna wants to add customes to anges in the worksheet Apply the custom name Mortgage Payment to the A: Apply the custom name interest Rate_Schedule to the range AW 026 Apply the custom nome Amortization Schedule to the range 2121 11. Anames to the cells in the range DS:07 by selecting the rage CD and crimestrom the selection in the values in the left column the denne 12 Hannah wishes to work, so that she intake any changes to the valu. However, he made tone Page 2 of 5 113% 1378 words Focus - g MacBook A Word File Edit View Insert Format Tools Table Window Help AutoSave SUBE. Instructions. Saved to my Mac Home Insert Draw Design Layout References Tell me Share Comments A. A Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe POF Signatures 12. Hannah wishes to protect the worksheet, so that she doesn't make any accidental changes to the values. However, since she made assumptions about the price of the clinic, the down payment, and the mortgage interest rate, she wants to be able to update these values in the protected worksheet. a. Select and unlock the range B5:56. b. Select and unlock cell D3 Protect the Clinic Mortgage worksheet without a password 13. Hannah had previously hidden a worksheet containing data on her other financial obligations. Now she wants to review the data in that worksheet along with the data she just calculated Unhide the Outstanding Loans worksheet 14 Switch to the Outstanding Loans worksheet In cell 09, create a formola without using a function that determines the total interest associated with the loan. First ultiply the value in cell 07 (the number of years) by the value in cell (the monthly payment) and by 12 (to convert the yearly terms to monthly terme, and then subtract the value in cell 35 (the loan amount) Copy the formula without the formatting in cells into the range CHES 15 Hannah wold like to be able to see the remaining balance of her other lansat the end of the current year In cell B12. create a formule using the PV function to determine the outstanding balance of the loans at the end of the current var using the parameters below For the rate parameter, use the value in cell 6 (the yearly interest rate of the mortgaga) divided by 12 for the root subtract the value in cell 011 (the current O Focus Page 3 of 5 1378 words g Tools Table Window Help Word File Edit View Insert Format Instructions... - Saved to my MacQ 5.UBE. AutoSave Insert Home Draw Design Layout References Tell me Share Comme A 0 Paste Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures Home A Shelly Cashmon Excel 2016 | Module 4: SAM Project tb b For the per parameter, subtract the value in cell 011 (the current year of the loan) from the value in cell 07 (the total number of years of the loan), and multiply that by 12 For the pet parameter, use the value in cel BB (the monthly payment), putting a negative sign before this value to make the outcome of the PV function positive Copy the formula without the formatting from cell B12 to the range C12:E12 Your workbook should look like the Final Figures below. Save your changes, close the workbook, and then exit Excel Follow the directions on the SAM website to submit your completed project 1 Final Figure 1: Clinic Mortgage Worksheet PT Associates Page 4 of 5 1378 words D Focus 113 O Word File Edit View Insert Format Tools Table Window Help AutoSave ORT A SUDE: Instructions_... - Saved to my MacQ Home Insert Draw Design Layout References Tell me Share Com A Font Paragraph Styles Dictate Sensitivity Create and Share Request Adobe PDF Signatures Paste Shelly Cashman Excel 2016 L Module SAM Proctib Final Figure 2: Outstanding Loans Worksheet PT Associates 3 Loan Type Year borrowed 4 Price 5 Loan Amount 6 Rate 7 Term (Years & Monthly Payment Total interest 10 Total Cont 11 Current Year of loan Loan Balance and of Current year Car Equipment Condominium College 2010 2011 2010 $ 25,000.00 5 70,000.00 $100,000.00 10,000.00 $ 20.000.00 S5,000.00 $ 300,000.00 $ 60,000.00 5.255 6359 4.5.SON 2.SSON 10 30 13 $ 373 375 60.58 1,228345 40142 $ 3.745.543 1949.0 202,202.13 5 12.267. 5214502,202.11 2.267. 12 SA 420.03 20:290275 0.20 $ 11,902 Dom Clint Mortgage Outstanding Lan Page 5 of 5 1378 words Focus UW de layout Formulas Dat Review View Help P Search Shar A Dame Une in forma Manager Create from Section to sam Recently and logical Test & Look & Math More Ud- Tine Heterence Trg Functions Show Formula Parace Dependents by Enor Checking Remove Arrow- con Wale Window Con Fonction 811 PT Associates 3 Date tem 5 Price 6 Down Payment 7 Loan Amount Mortgage Loan Payment Calculator 15 Aug 18 Rate Clinie Term (Years 375.000.00 Monthly Payment 75,000.00 Total interest 300,000.00 Total Cost Year 1 2 5.250X 15 2,41163 134,093.97 434,093.97 5 5 $ AM $ s s 9 10 Rate 11 12 13 14 15 T6 17 Varying Interest Rate Schedule Mootd Payment Total Tral Cost $ 134,093.97 $ 434,093.97 3.000% 3250% 3:500% 3.750% 4.000% 4.250% 4.500 4.750% S.ODOS 5.250% 5.500% 5.7SON 000 6.250 6500% 4 5 6 7 8 9 10 11 12 13 14 15 Amortization Schedule Bestinn Balance Endine balance 300,000.00 286,488 35 286,458.35$ 272.250.02 s 272.250 025 257,245.93 $ 257,245.935 241,434.89 s 241.434.89 $ 224.773.50 $ 224,773.50 $ 207,216.03 5 207,216.035 188,714.29 5 188.71425 169.217.49 s 169,217.495 148,672.11 5 148,672.11 $ 127,021.76 s 127.021.26 $ 104,20702 $ 104,207.02 80,165 26 $ 80,165.26 5 54,830.48 5 54830 48 5 28.133.16 $ 28.133.16 S Subtotal $ Down Payment Total Cost 19 20 21 22 23 24 25 26 27 28 29 Documentation Clinic Mortgage Type here to search O RI E 9 Home
