O Search the web A PDF a f + O O X AutoSay On 4 5 6 B MAT-144-RS-Online-Major_Assignment* 1202_Excel_20240419120(1) (version 1).xIsb... . Saved V Search Q ZP O X File Home Insert Page Layout Formulas Data Review View Automate Help Com-pants ZS IN M A R W U Y Desktop B14 i XVfxv A B D E F G H I J K L M This PC co Start by looking up the rate to use as your APR. Use the chart on the Mortgage Rates tab, which contains historical data on 30-year fixed mortgage rates. Libraries 10 11 APR Year 2014 Control Pa.. APR Month 9 14 Interest Rate 15 Recycle Bin 98 16 4 Now, using the Student Loan Amount given below, perform each of the calculations indicated. Refer to the Financial Control Pa. 17 formulas section provided on the Instructions Word document as needed. Do not use the Excel built-in =PMT() 18 function. Owner 19 Format all financial amounts as Currency with the $ symbol and 2 decimal places, and all n and t entries as Number 20 with 0 decimals. VideoPad . Unsubsidized Student Loan (determine the Subsidized Student Loan (determine the monthly payment required for a 10-year loan HP Print a.. monthly payment required for a 10-year loan) that accrues simple interest while in school for 4 2 PC HelpSo.. Initial principal amount to repay (P) $32,738.00 22 23 Your APR (r) Complete the interest rate entry above 24 Number of years while in school success_st.. Additional interest accrued while in 25 school Doxillion ... 26 New principal amount to repay (P) 27 Number of payments per year (n) 28 lumber of years (t) US Crude Oil I... The monthly loan payment (PMT) The monthly loan payment (PMT) 29 based on the initial principal based on the new principal amount 30 Total amount paid on the loan Total amount paid on the loan Interest paid on the loan (including the] Interest paid on loan 31 interest accrued while in school). 32 10:02 AM 34 Snip & Sketch Wednesday 35 9/25/2024 Snip saved to clipboard Grading Sheet - Rubric Sheet1 Income and Projection Student Loans Mortgage Rates Credit Cards Annual Budget + Select here to mark up and share the image Ready 1x Accessibility: InvestigateI PC HelpSo. L R i3 o || US Crude Oil I... 10:03 AM (e LN [l O search the web 1 Enter your full name to the right. If your full name is less than 10 letters long, add additional letters X' at the end until you reach length 10. 2 Below, you will find an annual income that someone could expect to eam once they start their career. Because they expect to enter into this career in the future rather than g | today, you will need to project what that income will be five years in the future so you o | caninvestigate savings, investments and loan repayments. 11 Current Income $60,736.00 22 Below, you will generate the prajected income 5 years in the future You will base your projection on CPI values that you look up. Use this procedure to look up the CPI value: 1. Go to Bureau of Labor Statistics page link https://data.bls.gov/cgi-bin/surveymost?cu 2. Check the box to the left of text "U.S. city average, All items - CUURO000SAQ" 3. Press the "Retrieve Data" button at the bottom of the list. This should take you to a CPl table for about the last 10 years. (You may need to change the values shown by using the "Change Qutput Options\" at the top of the page). First, fill in the CPI value for the given month (1=Jan, 2 = Feb, etc.) and year. Then, fill in the CPI values for the next 8 years, advancing exactly one year for each value. For example, if you start in| February 2011, your next CP| value will come from February 2012, Format your CPI values as Number with 3 decimals of precision. CPIValue $60,736.00 Year 2012 2013 Month Number 29 8 30 8 Grading Sheet - Rubric ~ Sheet1 ~ Income and Projection | Student Loans Mortgage Rates Ready T Accessibility: Investigate A avtosae @D B 9D Tv B & S MAT-14-RS-Online-Major_Assignment*1202_Bxcel_20240419%120(1) (version 1)clsbu.. - Saved Fle Home Insert Pagelayout Formuas Data Review View Automate Help c29 ol fir =B11 A B C D E F 1 G H I I K L M = Assignment Advisory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. (This is provided free by GCU; contact the Help Desk for more information and help installing the software.) Using an earlier version of Excel or a different spreadsheet program may result in missing or corrupted template elements. Copying cells from or into this template may likewise result in corrupted data. Legend If a cell is shaded You should Blue Enter a text response Green Enter a number Gold Enter an Excel formula Any other color Make no changes 2b Now, from the year and CPI values, find the slope and y-intercept of the best-fit line for your CPl values. Format your values as Numbers with 3 decimals of precision Slope (m) Y-Intercept (b) 2C Use your slope and y-intercept to project the CPI 5 years in the future based on the Year you are Projecting the Income to; calculate the 5 year Inflation Rate based on the last CPI value from the table in 2a and the Projected CPI Value; apply the 5-year Inflation Rate to your Current Income to find a 5-year Income Projection; and calculate the Projected Manthly Income. Use Excel formulas with cell references when appropriate for all gold cells. Format the Projected CPI Value as a Number with 3 decimal places and the 5-year Inflation Rate as a Percentage with 2 decimal places. Format Your Current Income, Your 5-year Income Projection, and Projected Monthly Income as Currency with 2 decimal places. Year you are Projecting your Income to (5 years after the last CPI value from 2a) Projected CPI Value for the Year you are Projecting to| The 5-year Inflation Rate based on the last CPI value from 2a and the Projected CPI value Your Current Income (cell referenced from above) Your 5-year Income Projection Projected Monthly Income v Credit Cards | Annual Budget + C ] e on @) Fle Home Insert Pagelayout Formulas Data Review View Automate Help e (v [B @ F MAT-144-RS-Online-Major_Assignment ~1202_Excel_202404194120(1) (version 1)xlsb... = Saved v P search 125 ~ S A B = D E F G H I J 1 |5 Below you will examine paying off credit card debt with the minimum payment. Use the 2 |information below to complete the problem. 3 4 | Discover both how much is paid and how long it takes to pay off a $2200 credit card debt Legend 5 | paying the minimum payment which is the maximum of 2.5% of the Current Balance or a fixed If a cell is shaded You should 6 | amount of $30. The credit card has 21% APR compounded monthly. Find out how long it takes Enter a text response 7 | to pay this off and how much will be paid total. Assume payments are made on the current Enter a number balance at the beginning of each period (month), before interest is accrued and assuming that Enter an Excel formula 9 the card is not being used for any additional charges. Any other color Make no changes 10 11 12 13 R P 54 These are the starting parameters, use cell references to these values in your 14 Starting Balance 2,200 formulas in part Sh. Enter the appropriate values from above. 15 Payments per Year 12 16 Minimum Payment Format all percentages as Percentages to two decimal places, all currency values as 17 | Percentage of the Current 2.50% Currency to two decimal places, and Payments per Year as Number to zero decimal 18 Fixed Minimum $30.00 places. 19 20 5b Below enter Excel formulas with cell references and appropriate built in Excel functions to calculate the Current Balance, Payment, Balance 5 Use the table from 5b to calculate the Number of Years to 2 Pay OFf (based on the number of Months found), Total 21 After Payment, and Interest for each Month. You should continue to fill in the rows until you reach a balance of 0. Note that the Current Balance for . . . Amount Paid, and Amount of Total Interest Paid. Month 1 is completed for you. 22 o Format the Number of Years to Pay Off as a Number with 2 23| Format all cells as Currency with 2 decimal places. = P decimal places and the Total Amount Paid and Amount of Month Current | Payment |Balance After| Interest for Total Interest Paid as Currency with 2 decimal places. 24 Balance |seenotestside| Payment | this month. 25 1 $2,200.00 Number of Years to Pay Off $30.00 26 2 Total Amount Paid 27 3 Amount of Total Interest Paid 28 4 29 5 ;? j Note: The amount you pay at the start of the month is the = s larger of the fixed minimum payment (D18) and the - 0 percentage (D17) of the current balance (column B). You may 34 10 use t):\\ev=MIN.[) func'tinr\\ t'u have E?cel do this or manually Grading Sheet - Rubric ~ Sheet1 Income and Projection StudentLoans = Mortgage Rates ~ CreditCards annual Budget Ready 1% Accessibility: Investigate