Your uncle has $90,000 that he wishes to invest now in order to use the accumulation for purchasing a retirement annuity in five years (assume this will be purchased in the beginning of Year 6).
After consulting with his financial adviser, he has been offered four types of fixed income investments, labeled as A, B, C, and D. Investments A and B are available at the beginning of each year. Each dollar invested in A at the beginning of a year returns $1.20 two years later.
Each dollar invested in B at the beginning of a year returns $1.36 three years later. Investments C and D are only available at one time in the future. Each dollar invested in C at the beginning of year 2 returns $1.66 at the end of year 5.
Each dollar invested in D at the beginning of year 4 returns $1.12 at the end of year 4. In addition, your uncle can simply keep money in his savings account and earn 2% annually.
Your uncle is obligated to make a balloon payment on an existing loan, in the amount of $24,000, at the end of year 3. He wants to cover that payment out of these funds as well.
Construct and solve an Optimization model for this problem in Excel USING ANALYTIC SOLVER PLATFORM.
a. Answer the following questions:
i. Which investments are being used?
ii. How much profit will your uncle have made at the end of the five years?