- 1 (30.63 KiB) Viewed 76 times
calculate the MONTHLY payments on the car loan described in
A2:B4.
Task 2 A10:D22 contains Loan Payments
for two loans that are due on certain dates. E11 contains a
dropdown box with months. In F11, write a formula that will lookup
the date chosen in E11, determine with either Loan 1 or Loan 2 (or
both) must be paid that month and total the payments due on loans
for that month. This should be a robust formula. HINT: Don't forget
about the IFERROR function.
Task 3 A26:B35 contains years and a
space for the annual 401k employee limit (the MOST an employee can
add to their 401k plan in a given year and be allowed to deduct
that amount for tax purposes, which is established by Congress).
This amount is expected to increase by the rate of inflation each
year. In B25, create a NAMED RANGE called "Inflation" and using a
financial function in B28:B35, increment the annual 401k limit each
year by inflation. The nper MUST be robust and not "hardwired."
Task 4 Using a financial function in
B28:B35, increment the annual 401k limit each year by inflation.
The nper MUST be robust and not "hardwired."
Task 5 Name cell B24 "Raise."
Task 6 In C28:C35 calculate the
employee's predicted salary for 2019 through 2026. Use a finance
function to do this and make it robust!
Task 7 In D27:D35, calculate the
employee's annual deposit to their 401k. This amount will the lower
of 15% of their salary or the annual limit established by Congress.
Use a function to this and it should not be an IF statement.
A B C D E וד F 1 2 35000 Amount borrowed 4 # of years of loan 5% annual interest rate on loan min D 6 7 35000 Amount borrowed 4 # of years of loan 5% annual interest rate on loan 8 9 Dec-16 500 10 Date Loan #1 Date Loan #2 11 Jan-17 500 Nov-16 300 12 Feb-17 500 Dec-16 300 13 Mar-17 500 Jan-17 300 14 Apr-17 500 Feb-17 300 15 May-17 500 Mar-17 300 16 Jun-17 500 Apr-17 300 17 Jul-17 500 May-17 300 18 Aug-17 500 Jun-17 300 19 Sep-17 Jul-17 300 20 Oct-17 500 Aug-17 300 21 Nov-17 500 Sep-17 300 22 Dec-17 500 Oct-17 300 23 24 Raise 5% 25 Inflation Annual 401K Limit Employee Deposit 26 Year (Employee) Salary to 401k 27 2018 18500 100000 28 2019 29 2020 30 2021 3%