Question

In: Finance

1. You have recently earned the Certified Financial Planner ® designation and have increased your client...

1. You have recently earned the Certified Financial Planner ® designation and have increased your client base because of it. You have just added some members of the Gilligan’s Island cast to your list of clients. Some of their stories and investment goals are described below:

A. Bob Denver (Gilligan) received a large lump sum as compensation for the Season 3 DVD in the amount of $2.1 million. He wants to purchase a home on the beach in Northern California. You advise him to use the entire $2.1 million plus a $3.2 million loan from a financial institution that will charge him a 6.1% annual interest rate over ten years. His first payment is due on August 5, 2019.

Determine the monthly payment on the loan.

  1. Alan Hale Jr (the Skipper) currently has $3.8 million invested in a well-diversified portfolio of securities earning 12%. Alan has two children (ages 15 and 10) and would like to create a trust fund for each child so that each would have $10 million on their 25th birthdays (10 and 15 years from today). Alan would like to save quarterly for the next 5 years to provide for his children.

Determine the quarterly amount (beginning today) Alan must save to reach the goal for both children assuming the new funds will earn 12% annually.

C. Tina Louise (Ginger) has recently seen her entertainment income reduced to zero because the industry has typecast her. Luckily, she has $10 million invested in a retirement fund earning 10% per year. She is considering retirement in three years when she turns 60. Tina believes she can live until she is 90.

Determine the monthly retirement income if she earns 6% during retirement years if she wants to leave $1 million to the Howell Estate on the date of her death.

Solutions

Expert Solution

a) Monthly interest rate is 6.1%/12 = 0.508%

The Monthly installment is calculated using the formula

(P*R*(1+R)^N)/(((1+R)^N)-1)

P=3.1 Million

R=0.508%

N=10*12 = 120 Months

Hence using the formula Installment = 34,572 $ per month

b) The quarterly return is assumed to be 12%/4 = 3%

The table below contains the debt repayment schedule:

Time Amount at the beginning Earnings Recurring Deposit Cash Out Flow
1 3800000 114000 34792.4 0
2 3948792.401 118463.8 34792.4 0
3 4102048.574 123061.5 34792.4 0
4 4259902.432 127797.1 34792.4 0
5 4422491.906 132674.8 34792.4 0
6 4589959.064 137698.8 34792.4 0
7 4762450.237 142873.5 34792.4 0
8 4940116.145 148203.5 34792.4 0
9 5123112.03 153693.4 34792.4 0
10 5311597.792 159347.9 34792.4 0
11 5505738.127 165172.1 34792.4 0
12 5705702.672 171171.1 34792.4 0
13 5911666.153 177350 34792.4 0
14 6123808.539 183714.3 34792.4 0
15 6342315.196 190269.5 34792.4 0
16 6567377.052 197021.3 34792.4 0
17 6799190.765 203975.7 34792.4 0
18 7037958.889 211138.8 34792.4 0
19 7283890.057 218516.7 34792.4 0
20 7537199.159 226116 34792.4 0
21 7798107.535 233943.2 34792.4 0
22 8066843.162 242005.3 34792.4 0
23 8343640.858 250309.2 34792.4 0
24 8628742.484 258862.3 34792.4 0
25 8922397.16 267671.9 34792.4 0
26 9224861.476 276745.8 34792.4 0
27 9536399.721 286092 34792.4 0
28 9857284.114 295718.5 34792.4 0
29 10187795.04 305633.9 34792.4 0
30 10528221.29 315846.6 34792.4 0
31 10878860.33 326365.8 34792.4 0
32 11240018.54 337200.6 34792.4 0
33 11612011.5 348360.3 34792.4 0
34 11995164.24 359854.9 34792.4 0
35 12389811.57 371694.3 34792.4 0
36 12796298.32 383888.9 34792.4 0
37 13214979.67 396449.4 34792.4 0
38 13646221.46 409386.6 34792.4 0
39 14090400.51 422712 34792.4 0
40 14547904.92 436437.1 34792.4 10000000
41 5019134.471 150574 34792.4 0
42 5204500.906 156135 34792.4 0
43 5395428.334 161862.9 34792.4 0
44 5592083.585 167762.5 34792.4 0
45 5794638.494 173839.2 34792.4 0
46 6003270.05 180098.1 34792.4 0
47 6218160.552 186544.8 34792.4 0
48 6439497.77 193184.9 34792.4 0
49 6667475.104 200024.3 34792.4 0
50 6902291.758 207068.8 34792.4 0
51 7144152.912 214324.6 34792.4 0
52 7393269.9 221798.1 34792.4 0
53 7649860.398 229495.8 34792.4 0
54 7914148.611 237424.5 34792.4 0
55 8186365.47 245591 34792.4 0
56 8466748.835 254002.5 34792.4 0
57 8755543.701 262666.3 34792.4 0
58 9053002.413 271590.1 34792.4 0
59 9359384.886 280781.5 34792.4 0
60 9674958.834 290248.8 34792.4 10000000

Hence the deposit per quarter is 34792. The calculation has been done in excel using Goal Seek feature and changing the value of Deposit to ensure there is exactly 1000000 fund available at the end of the 15 years.

c) The total sum available when she turns 60 is :

Time Amount
0 10000000
1 11000000
2 12100000
3 13310000

1.331 Million USD

Using the PMT function in excel:

=PMT((6%/12),360,13310000,1000000)

=80,795 $

jeff jeffy answered 1 month ago
Next > < Previous

Related Solutions

You are now a certified financial planner who has recently graduated from college. Your parents are...
You are now a certified financial planner who has recently graduated from college. Your parents are helping you to get started in your new business. Since your parents own a midsize company, they decide that IF you can come up with a good retirement plan for the 275 employees that they employ, they will hire you as the financial planner for the business. They want to make sure that all the employees in the company have some medical benefits as...
You are now a certified financial planner who has recently graduated from college. Your parents are...
You are now a certified financial planner who has recently graduated from college. Your parents are helping you to get started in your new business. Since your parents own a midsize company, they decide that IF you can come up with a good retirement plan for the 275 employees that they employ, they will hire you as the financial planner for the business. They want to make sure that all the employees in the company have some medical benefits as...
(c) You have commenced work as a Certified Financial Planner. Your supervisor has provided the following...
(c) You have commenced work as a Certified Financial Planner. Your supervisor has provided the following financial data for a new client Brant Jerome. The client turned 34 years old today and plans to retire when she turns 67. The client owns a diversified share portfolio which is valued today at $47,000. It is expected that this portfolio will earn (on average) 7% per annum indefinitely. Brant also has a superannuation account with a balance of $78,000 to which he...
(c) You have commenced work as a certified Financial Planner. Your supervisor has provided the following...
(c) You have commenced work as a certified Financial Planner. Your supervisor has provided the following financial data for a new client Brant Jerome. The client turned 34 years old today and plans to retire when she turns 67. The client owns a diversified share portfolio which is valued today at $47,000. It is expected that this portfolio will earn (on average) 7% per annum indefinitely. Brant also has a superannuation account with a balance of $78,000 to which he...
You are a graduate financial planner and have been sitting on client interviews with a more...
You are a graduate financial planner and have been sitting on client interviews with a more Senior Financial Planner. As part of this process you have been recording notes and collaborating on any strategy development with the Senior Financial Planner. David & Karen Rubble are new clients and have come to you early in the financial year seeking your advice in regards to their pending retirement. After a fact-finding session you uncover that David is 61 years old and is...
You are a financial planner and your private client has given you $10,000,000 to invest for...
You are a financial planner and your private client has given you $10,000,000 to invest for them. Your task is to study the current economic and geopolitical conditions and to invest his money for the highest return. What sector(s) do you think will provide the greatest return and what stocks or other investments within those sectors would you invest in?
You are a recently designated accountant. As a result of having your designation, you have been...
You are a recently designated accountant. As a result of having your designation, you have been hired as the controller at a national manufacturing company. Due to a recent economic slowdown, the company has been struggling to meet earnings targets. These targets are the basis for senior management bonuses. You report directly to the CFO. This is your second month with the company; however, it is your first year end (December 31). The auditor will be coming to audit the...
Suppose you work as a financial planner and have a client, Ms. Vanessa Ives, who was...
Suppose you work as a financial planner and have a client, Ms. Vanessa Ives, who was born in 1980 and wants to retire at age 60. Mortality tables indicates that Ms. Ives should expect to live to age 85. Ms. Ives wants a fixed retirement income that has the same purchasing power at the time she retires as $50,000 has today (2018).(You understand that the real value of his retirement income will decline annually after she retires, but consider that...
Taking the role of a financial planner, let's say you have a client (named Jane), who...
Taking the role of a financial planner, let's say you have a client (named Jane), who is a single mother of two in her late twenties. Due to attractive interest rates, Jane just bought her first house. She lives in Denton but drives to work each day in Frisco in a vehicle that she purchased, but is not fully paid for. What types of insurance does Jane need to have protection (mitigate risks) in all aspects of her life? Describe...
Taking the role of a financial planner, let's say you have a client (named Jane), who...
Taking the role of a financial planner, let's say you have a client (named Jane), who is a single mother of two in her late twenties. Due to attractive interest rates , Jane just bought her first house. She lives in Denton, but drives to work each day in Frisco in a vehicle that she purchased, but is not fully paid for. What types of insurance does Jane need to have protection (mitigate risks) in all aspects of her life...
ADVERTISEMENT
Subjects
ADVERTISEMENT
Latest Questions
ADVERTISEMENT