MAT 144 Grand Canyon University Monthly Budget Savings and Loan Analysis

User Generated

nirentr_ohggresyl

Mathematics

MAT 144

Grand Canyon University

MAT

Description

In Major Assignment 1, you created a monthly budget, which included a recurring cost for utilities.Here, you'll consider making some energy-saving home improvements and compare your potential savings against paying off the cost of those improvements.

Unformatted Attachment Preview

Major Assignment 2 Grading Sheet Competency Name Requirements for full credit You have entered your full name in the field provided. The interest rates you have entered come from the mortgage rates table and match those for the months and years provided. You have explicitly formatted the cells to display as Percentage with 2 decimal places of precision. Your Electric, Gas, Water, and Other entries are reasonable values, with at least two nonzero entries. For zero entries, you have explicitly entered values of 0. Monthly Costs Your Total Cost and Monthly Savings formulas are correct and use and Savings appropriate cell references. All cost cells are formatted as Currency showing the $ symbol and with 2 decimal places of precision. You have brought forward your monthly savings amount, using Excel formulas. Your number of contributions per year and number of years entries are correct. Savings and Savings Table Your formulas for total amount saved, total contributions, and total accrued Loan Analysis interest are correct and use cell references. All cells are explicitly formatted with the format given in the last column of the table. You have entered the correct number of contributions per year and number of years. Interests Rates Loan Table Your formulas for payment amount, total amount paid, and total amount of interest paid are correct and use cell references as inputs. All cells are explicitly formatted with the format given in the last column of the table. You have correctly brought forward your savings and loan amounts, using cell references. Your savings and loan cells are explicitly formatted with the format given in Comparison the last column of the table. You have answered the comparison questions correctly, answering either "yes" or "no" for each one. Your reference CPI is correct for the month and year given. Your next-year CPI, month, and year are correct. Inflation Rate Your inflation rate calculation is correct. Calculation Your CPI values and inflation rate are explicitly formatted as indicated in the instructions. Budget Cost Projection Budget Cost Projection Budget Projections Monthly Savings Conversions You have correctly entered your Budget Total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. Your "value of t" entries are correct. Your 1-year, 5-year, and 10-year projections are correct Excel formulas using cell references. Your percent increase calculations are correct Excel formulas using cell references. Your Current Budget, Projected Budget and Percent Increase cells are formatted as indicated in the instructions. You have brought forward your monthly savings amount from the Savings and Loan Analysis sheet, using an Excel formula with a sheet and cell reference. You have entered the first two letters of your first and last names, using the letter M if one or both names consist of only one letter. You have chosen appropriate countries from the list provided below the table, using the procedure described in the instructions. You have entered the date(s) on which you looked up the exchange rates for your currencies, and all dates are within 2 weeks of the due date of your assignment. You have entered both the full name of your country's currency and the correct currency code as indicated on the website. Currency You have provided each exchange rate to at least 5 significant digits, and the Conversions exchange rate matches the rate for the date you looked it up. Your savings amount in the foreign currency is a correct Excel formula, using cell references. Your calculation of the value of foreign currency units into dollars are correct Excel formulas, using cell references. (Note that the amount to convert is autogenerated and may differ from the amount shown in assignment resources.) The cells containing your dates, savings amounts, and value of foreign currency converted to dollars are correctly formatted as specified in the last column of the table. nt 2 Grading Sheet (optional for student use) Did you meet the requirements? Points Your points possible 1 3 3 4 4 6 3 6 18 18 6 18 15 6 6 3 Subtotals 120 1 3 3 3 0 Scoring comments 2 3 6 6 7 Subtotals 34 0 2 4 4 4 8 4 8 8 12 Subtotals 54 0 Totals 208 0 Percentage 100.00% 0.00% Scaled out of 100 100.00 0.00 1 In Major Assignment 1, you created a monthly budget, which included a recurring cost for utilities. Here, you'll consider off the cost of those improvements. Below, you'll start by entering and adding up the costs of your electric, gas, water, and other energy utilities. Then, give the next 5, 10, and 15 years if you contribute your monthly savings into an account with a given APR. Here, you'll use the f per year for t years and earning interest at an annual percentage rate of r, the total amount A accrued after t years is give A = P*((1+r/n)^(n*t) Next, you'll develop a cost to install energy-efficient improvements (installing energy-efficient doors and windows, adding in payment if you were to finance the installation cost by a loan of 5, 10, or 15 years. Here, you will use this formula: give off the loan with n payments per year for t years (with payments made at the end of each period) is given by (in Excel forma PMT = P*(r/n)/(1 For all the above calculations, you will look up rates in the following historical table of 30-year fixed mortgage rates, based http://www.freddiemac.com/pmms/ (Mortgage Rates Assignment Advisory: You must use the la 2 Enter your full name here provided free by GCU; contact the Help Desk version of Excel or a different spreadsheet prog from or into this tem (If fewer than 9 letters, add additional arbitrary letters) Savings 3 Look up three interest rates from the historical mortgage rate table, formatting them as Percentage with 2 decimal places. Make sure to enter these as percentage values. For example, 4.03 in the table is 4.03% or 0.0403. APR Year Your full name entry must be longer APR Month Your full name entry must be longer Interest Rate Electric Gas 4 Enter, or estimate, your monthly utility costs, then calculate your total monthly cost and monthly savings. Format all cells as Currency showing the $ symbol and with 2 decimals of precision. Water Other Total Cost 4 Enter, or estimate, your monthly utility costs, then calculate your total monthly cost and monthly savings. Format all cells as Currency showing the $ symbol and with 2 decimals of precision. Monthly Percent Savings Complete the first interest rate entry in section 3 above Monthly Savings (total cost times percent savings) 5 Complete this table for your 5-year, 10-year, and 15-year savings Contribution amount (P) (Bring forward your Monthly Savings amount, using a formula, for each entry) APR from the table (r) Calculation #1 (5-year savings) Calculation #2 (10-year savings) Complete the second interest rate entry in section 3 above Complete the second interest rate entry in section 3 above Number of contributions per year (n) Number of years (t) Total amount saved (A): Total contributions: Total accrued interest: Loan 6 Continue by completing this table for your 5-year, 10-year, and 15-year loans, based on the principal and interest rates given with monthly payments Calculation #1 (5-year loan) Calculation #2 (10-year loan) Loan principal (P) Your full name entry must be longer Your full name entry must be longer APR from the table (r), with a slightly higher rate for longer loans Complete the third interest rate entry in section 3 above Complete the third interest rate entry in section 3 above Number of contributions per year (n) Number of years (t) Payment amount (PMT): Total amount paid over the time of the loan: Total amount of interest paid: Comparison 7 Use Excel formulas to transfer your amounts from above Total savings from energy improvements Total loan payments Have you broken even at this point (yes or no)? ("Breaking even" here means that your total savings outweigh your total loan payments.) After 5 years After 10 years ties. Here, you'll consider making some energy-saving home improvements and compare your potential savings against paying gy utilities. Then, given a percent savings due to your energy-saving improvements, you'll calculate how much you'll save over PR. Here, you'll use the following formula for your calculations: given an amount P contributed at the end of each of n periods rued after t years is given by (in Excel format): A = P*((1+r/n)^(n*t)-1)/(r/n) ors and windows, adding insulation, upgrading to more efficient appliances or lights, and so on) and then calculate a monthly use this formula: given a loan principal amount P and an annual interest rate of r, the payment amount PMT required to pay is given by (in Excel format): MT = P*(r/n)/(1-(1+r/n)^(-n*t)) ed mortgage rates, based on the years and months specified in step 6 below. w.freddiemac.com/pmms/pmms30.html Mortgage Rates) sory: You must use the latest desktop version of Excel for Microsoft 365 for this assigment. (This is CU; contact the Help Desk for more information and help installing the software.) Using an earlier ifferent spreadsheet program may result in missing or corrupted template elements. Copying cells from or into this template may likewise result in corrupted data. Your full name entry must be longer Your full name entry must be longer Your full name entry must be longer Your full name entry must be longer 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 Calculation #3 (15-year savings) Format the entries in each row as… …Currency with 2 decimal places Complete the second interest rate entry in section 3 above …a Number with 0 decimal places …a Number with 0 decimal places …Currency with 2 decimal places …Currency with 2 decimal places …Currency with 2 decimal places Calculation #3 (15-year loan) Format the entries in each row as… Your full name entry must be longer Complete the third interest rate entry in section 3 above …a Number with 0 decimal places …a Number with 0 decimal places …Currency with 2 decimal places Make no changes …Currency with 2 decimal places …Currency with 2 decimal places After 15 years Format the entries in each row as… …Currency with 2 decimal places …Currency with 2 decimal places against paying ou'll save over ch of n periods te a monthly quired to pay Your name (brought forward from the Savings and Loan Analysis sheet): 0 8 On the Monthly Budget sheet in Major Assignment 1, you evaluated your current expenses. Here, you will project your bu rate that you develop from values in the Consumer Price Index. As a first step, look up the CPI value for the given month and year as well as the CPI value one year later; then, calculate values. 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 (or use link below) 2. Check the box to the left of text "U.S. city average, All items - CUUR0000SA0" 3. Press the "Retrieve Data" button at the bottom of the list. This should take you to a CPI table for about th Here, format your CPI entries as Number with 3 decimals of precision, and format your yearly inflation rate as a Percentage w (CPI Values) CPI Value Month Your full name entry must be longer Reference CPI CPI one year later Yearly inflation rate (r) 9 Next, enter your budget total from cell G21 of the Monthly Budget sheet from your Major Assignment 1. Then, use the fo formula to project your monthly budget forward 1, 5, and 10 years into the future: A = B*(1+r)^t where A is the budget after t years; B is the initial budget; and r is the yearly inflation rate. Here, also calculate how m budget is in percent than your initial budget. Format your Projected Budget entries as Currency with the $ symbol and 2 dec precision; format your Percent Increase cells as Percentages with 2 decimals of precision. Value of t Current Monthly Budget (B) Monthly Budget next year Monthly Budget in 5 years Monthly Budget in 10 years Projected Budget ent expenses. Here, you will project your budget costs forward, using an inflation CPI value one year later; then, calculate a yearly inflation rate based on those CPI bin/surveymost?cu (or use link below) CUUR0000SA0" s should take you to a CPI table for about the last 10 years. t your yearly inflation rate as a Percentage with 2 decimals of precision. Year Your full name entry must be longer m your Major Assignment 1. Then, use the following Legend If a cell is shaded You should Blue Enter a text response Green Enter a number Enter an Excel formula Make no changes Gold Any other color tion rate. Here, also calculate how much larger each ies as Currency with the $ symbol and 2 decimals of Percent Increase over Current Budget 10 On this second conversion sheet, you will convert your monthly savings into the equivalent amounts in several foreign amount of the local currency into the equivalent number of US dollars. Start by transferring your monthly savings from the Savings and Loan Analysis sheet, using an Excel formula that reference Your monthly savings in dollars 11 Now, from the list below the table below, select four countries that start with the first two letters of your first and last names. If your first or last name is only one letter long, use the letter M as the second letter of each name that is one letter long. If there is no country starting with a particular letter or you have run out of countries to choose from for a particular letter, go to the next letter of the alphabet that you still have available choices for and select a country starting with that letter. (If you are at the letter Z, go back to A.) For each country, identify the name of the country's currency, the currency code (based on the ISO-4217 standard), and th following web page: https://www.xe.com/currencyconverter (Currency Converter) Then, convert your monthly savings above into this currency and a given number of units of the local currency into dollars. formulas that use a cell reference for the exchange rate; you may not use the currency converter link for this calculation (a your calculation there). Add special formatting as indicated in the last column of the table. Other entries may use general formatting. An example is provided for you. Note that this country is not available for you to choose from the list. Example The letter T Country starting with the letter (or next available letter) Tajikistan The date that you looked up the conversion rate (must be within 2 weeks of your assignment due date) Full name of the country's currency as listed on the XE website Currency code (ISO-4217) 5/23/2020 Tajikistani somoni TJS First letter of your first name Second letter of your first name Exchange rate for the currency to at least 5 significant digits (or exact rate if there are fewer than 5 significant digits) 10.26863117 Your savings in the country's currency. Note that you must enter a formula here and then format the cell to display the currency code; do not enter text in this cell. TJS 0.00 Your full name entry must be longer $97.38 Choose your countries from this list Afghanistan Cambodia Guatemala Lebanon Albania Canada Guernsey (UK) Liberia Algeria Cayman Islands (UK) Guinea Libya Angola Chile Guyana Macau (China) Argentina Armenia China Colombia Haiti Honduras Madagascar Malawi Aruba (Netherlands) Comoros Hong Kong (China) Malaysia Hungary Maldives Iceland Mauritania Azerbaijan Congo, Democratic Republic of the Costa Rica Bahamas Croatia India Mauritius Bahrain Cuba Indonesia Mexico Bangladesh Czechia International Moldova Monetary Fund (IMF) Barbados Denmark Iran Mongolia Belarus Belize Djibouti Dominica Iraq Isle of Man (UK) Bermuda (UK) Dominican Republic Israel Morocco Mozambique Myanmar (formerly Burma) Bhutan Bolivia Bosnia and Herzegovina Egypt Jamaica Namibia Eritrea Ethiopia Japan Jersey (UK) Nepal New Zealand Australia Botswana Falkland Islands (UK) Jordan Nicaragua Brazil Fiji Kazakhstan Nigeria Brunei Gambia Kenya Bulgaria Georgia Kuwait Burundi Ghana Kyrgyzstan North Korea North Macedonia (formerl y Macedonia) Norway Cabo Verde Gibraltar (UK) Laos Oman alent amounts in several foreign currencies and convert a given an Excel formula that references the appropriate cell directly: Legend If a cell is shaded Blue Green Gold Any other color You should Enter a text response Enter a number Enter an Excel formula Make no changes 4217 standard), and the exchange rate for $1, using the f the local currency into dollars. These calculations must be Excel verter link for this calculation (although you're welcome to check general formatting. om the list. First letter of your last name Second letter of your last name Format this entry as Date Currency with the country's currency code as a symbol Currency with the $ symbol Pakistan Switzerland Papua New Guinea Syria Paraguay Taiwan Peru Tanzania Philippines Poland Qatar Thailand Tonga Trinidad and Tobago Romania Tunisia Russia Turkey Rwanda Turkmenistan Saint Helena (UK) Uganda Samoa Ukraine Sao Tome and Principe Saudi Arabia Serbia United Arab Emirates United Kingdom Uruguay Seychelles Uzbekistan Sierra Leone Singapore Somalia Vanuatu Venezuela Vietnam South Africa South Korea Sri Lanka Sudan Suriname Sweden Wallis and Futuna (France) Yemen Zambia
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

View attached explanation and answer. Let me know if you have any questions.

Major Assignment 2 Grading Sheet
Competency

Name

Savings and
Loan Analysis

Budget Cost
Projection

Requirements for full credit

You have entered your full name in the field provided.

The interest rates you have entered come from the mortgage rates table and
Interests match those for the months and years provided.
Rates
You have explicitly formatted the cells to display as Percentage with 2
decimal places of precision.
Your Electric, Gas, Water, and Other entries are reasonable values, with at
least two nonzero entries. For zero entries, you have explicitly entered
Monthly
values of 0.
Costs and Your Total Cost and Monthly Savings formulas are correct and use
Savings
appropriate cell references.
All cost cells are formatted as Currency showing the $ symbol and with 2
decimal places of precision.
You have brought forward your monthly savings amount, using Excel
formulas.
Your number of contributions per year and number of years entries are
correct.
Savings Table
Your formulas for total amount saved, total contributions, and total accrued
interest are correct and use cell references.
All cells are explicitly formatted with the format given in the last column of
the table.
You have entered the correct number of contributions per year and number
of years.
Your formulas for payment amount, total amount paid, and total amount of
Loan Table
interest paid are correct and use cell references as inputs.
All cells are explicitly formatted with the format given in the last column of
the table.
You have correctly brought forward your savings and loan amounts, using
cell references.
Your savings and loan cells are explicitly formatted with the format given in
Comparison
the last column of the table.
You have answered the comparison questions correctly, answering either
"yes" or "no" for each one.
Your reference CPI is correct for the month and year given.
Your next-year CPI, month, and year are correct.
Inflation Rate
Your inflation rate calculation is correct.
Calculation
Your CPI values and inflation rate are explicitly formatted as indicated in the
instructions.
You have correctly entered your Budget Total from cell G21 of the Monthly
Budget sheet from your Major Assignment 1.
Your "value of t" entries are correct.
Budget
Projections

1

Budget Cost
Projection
Your 1-year, 5-year, and 10-year projections are correct Excel formulas using
Budget
cell references.
Projections
Your percent increase calculations are correct Excel formulas using cell
references.
Your Current Budget, Projected Budget and Percent Increase cells are
formatted as indicated in the instructions.
You have brought forward your monthly savings amount from the Savings
and Loan Analysis sheet, using an Excel formula with a sheet and cell
reference.
You have entered the first two letters of your first and last names, using the
letter M if one or both names consist of only one letter.
You have chosen appropriate countries from the list provided below the
table, using the procedure described in the instructions.
You have entered the date(s) on which you looked up the exchange rates for
your currencies, and all dates are within 2 weeks of the due date of your
assignment.
You have entered both the full name of your country's currency and the
correct currency code as indicated on the website.
Currency You have provided each exchange rate to at least 5 significant digits, and the
Conversions exchange rate matches the rate for the date you looked it up.
Your sav...


Anonymous
Excellent resource! Really helped me get the gist of things.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Related Tags