Please complete
the following questions in EXCEL. Please number and highlight your final answer
in yellow.
1.
You have an investment that is guaranteed to make the following
cash flows:
year 1: 1,000
year 2: 2,000
year 3: 3,000
year 4: 4,000
1A. If your discount rate is 8%,
what is the value of this cash flow today?
1B. If you invest the cashflows when you receive them and can earn
8%, how much will you have 8 years from now?
2. You want to purchase a 2022
Porsche Cayenne Coup that costs $77,400.
2A.Calculate your monthly
payment assuming that you finance the full amount ($77,400) for 6 years at an annual interest rate of
5.5%.
2B.
Construct a full amortization schedule in excel.
2C.
How much total interest will you pay over the lifetime of this loan?
2D.
If you decide to add an additional $200 a month to your monthly payment, how
much
will you save in interest over the
course of the loan?
2E. If you can only afford a $650
a month car payment, what is the max you can afford to finance given the same
loan terms (6 years, at 5.5%)?
3. You
just had a child! You anticipate that your child will be attending a 4-year
college at the age of 18. You will be expected to write a check for tuition
at the start of each of the 4 years while your child is in college. If
college tuition is 35,000 and is expected to grow at the average inflation rate
of 2%.
3A. How much will
you spend on tuition each of the 4 years?
3B. How much will
you need set aside today in order to meet the yearly tuition obligation. Assume that you can invest it
and earn 6% per year.
3C. If you invest
your money today in a riskier financial asset, which can earn 11%, how much will you have to set aside
today?
4. You are looking at an investment that will pay
$2,200 in 5 years if you invest $1,100 today. What is the implied rate of
interest?
5. You decide to start saving for retirement. You
deposit 10,000 a year, starting today. How much will you have after 15 years if
you can earn 8%?
6. You invest 10,000 for 5 years at 5%. What is the
effects of compounding (“interest on interest”)?
7. You have two loan options:
Option A: 5.6%, compounded monthly
Option B: 5.75%, compounded semi-annually
Which option do you pick, and why?
8. Assume you just turned 35. Your current salary is
$100,000, and you anticipate that you will receive 3% annual raises until you
retire at age 65. You plan on setting aside 8% of your salary each year for
retirement, starting one year from today. If you can earn a 12% return on your
retirement account contributions, how much will you have when you retire at age
65?
