Education Major Assignment
MyraliaRose3.
Unconfirmed803215.crdownloadMajor Assignment 2
Income and projection, student loans, credit cards, annual budget
Income and Projection
Enter your name in the blue cell in part 1 and a current income will populate in part 2.
Use the CPI Values website to look up the CPI values for your given month and year in part 2a.
Then fill in the appropriate months, years, and CPI values. Each row of the table should advance one year.
For part 2b, use appropriate Excel functions to calculate the slope and y-intercept. (Hint: Look back at the Income Analysis tab on MA1)
Income and Projection – part 2c
The Year you are projecting to represents 5 years after the last year in the CPI values table.
Projected CPI Value for the Year you are Projecting to = slope*Year+y-intercept
The five-year inflation rate is =(The projected CPI – last CPI value from 2a)/ last CPI value from 2a
Cell reference the Current Income
Your 5-year Income Projection is =your current income * (1 + inflation rate)
Projected Monthly Budget = Your 5-year Income Projection/12
Student Loans
Start by looking up the mortgage rates for the given months and years. Enter your rate as a percentage.
Example: 4.03 should be entered as 4.03%
Use the provided Student Loan Amount to complete the parts:
PMT = P*(r/n)/(1 - (1 + r/n)^(-n*t))
Total amount paid to the loan = PMT*n*t
Interest paid on loan = Total amount paid to the loan – Amount of the original loan
Interest accrued while in school follows simple interest: I=P*r*t
Credit Cards
Enter your starting parameters into the appropriate green cells.
Credit Cards – Part 5b
The Payment is the larger of the fixed minimum payment (D18) and the percentage (D17) of the balance. You can implement this by hand, or use the =MAX() function in your formula.
The Final Payment amount must be a payment that results in a $0.00 Balance After Payment
Balance After Payment = Current Balance – Payment
Interest = Balance After Payment * APR / Payments per Year
Credit Cards – Part 5c
Number of Years to Pay Off = Period for Last Payment / Payments per Year
Total Amount Paid = SUM(all payments made in the table)
Amount of Total Interest Paid = Total Amount Paid – Starting Balance
Annual Budget
Total Annual Cost = Frequency per Year * Cost
Total Annual Budget = SUM(Total Annual Costs)
Projected Total in 5 Years = Total Annual Budget * (1 + Inflation Rate)
Remaining Income = Your 5-year Income Projection – Projected Total in 5 Years
For the pie chart, highlight the items in ‘Budget Category” and in “Total Annual Costs.” Go to Insert in the toolbar, click on the pie chart icon, and choose your pie chart.
