Financial Planning Analysis
INCOME SCALER =8
Assume 360-day year and 30- day month in your analysis.
Your clients (a married couple), both just turned 40, have diligently run their family business for a decade and have paid off all of their debts. Recently, their business has grown into a stable stage that generates a growing income stream for the family. From your initial consultation with your clients, you learn that they plan to retire on the day after they turn 62. Their family income is $7,800*income scalarat the end of this quarter, and they expect their family income will grow at a steady rate of 1.0% every quarter until they retire.
To prepare for retirement, your clients deposit 18% of their quarterly income in a tax-deferred SEP IRA account that generates an annual rate of return of 12%, compounded daily.
Q1. Determine the cash flows pattern of the quarterly contributions to the SEP IRA account; and calculate and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Also, calculate the SEP IRA account balance upon their retirement. Verify your work on the SEP IRA account balance with the formula approach!
Step 1:To calculate the income scalar we added the last digit of both of our UMID which is 5+1 to get a total of 6 then we divided the number by 2 which gave us 3. Since the income scalar was less than 4 we added 5 to the calculated value so the grand total was 8.
Step 2: We used the family income of 7800 into the income scalar of 8 which gave us the current quarterly salary of 62400.
Step 3: We have calculated the next quarter’s salary by multiplying the current quarterly salary of 62400 by (1.01) as income is growing steadily at one percent every quarter.
Step 4: To calculate the EAR, we used (1+r)=(1+(0.12/360))^360. TheSEP IRA account that generates an annual rate of return of 12%, compounded daily. Assume 360-day year and 30- day month in your analysis. Therefore 1+r= 1.127472954. The r=0.127472954.
Step 5: To get the quarterly rate we first did 1+i=1.12747295^(¼)=1.030449074. Then we subtracted 1.030449074 by 1 to get i which is 0.030449074.
Step 6: To get the number of years of saving for the client we have taken 62-40. The 62 being the year they want to retire and 40 becing how old the client currently is. Ao the total number of years of savings was equal to 22. Then to get the number of quarterly savelings we have taken the 22 years multiplied by 4 since it’s based on a quarterly basis.
Going forward all cashflow steps are demonstrated in excel
Step 7: For us the N is equal to the number of quarters. B is equal to quarterly salary. C is equal to a deposit in a 401k account. 18% of the quarterly salary is deposited into the 401k account thus c is equal to b*0.18 which is the quarterly deposit into the 401k account.
Step 8: For the IRA account end value we used which gave us the $47022 for the first quarter.
Step 9: To calculate the IRA account saving at retirement we muliped end value by 1+r which is equal 1.127472954^ 22-n which gave us $584157.3519 at the end of retirement
Step 10:To calculator the SAP IRA upon retirement we used SUM all the retirement values and got a total of $5050397.292
*To verify the the FV we have used the excel FV function* EXPLAIN LATER
Question 2:
Taken the calculations from question 1 for IRA account adding brokerage account to it by adding 9% compounded daily. The effective annual rate us (1+r)=(1+(.09/360))^360 giving us 1.09416197. The r being 0.09416197.
Step 1: To get the quarterly rate for brokerage account 1+(i)=1.02275215. Thusi is equal to 0.2275215.
Step 2: Client contributes 7% of quarterly income to the brokerage accounts, so we did 62400*.07=$4368.00
Step 3: To get the remaining money from brokerage account to 529 account for his daughters college after deducting it we converted the brokerage account into a annual number of $17472.00, then we subtracted it by 10,000 which resulted in $7,472.00.
Step 4: To get the End value for the brokerage account we used
which equals 30923.56.
Step 5: To calculate the saving in brokerage we used the value we got earlier of 30923.56*1.09416197^22-1= 204646.18
Step 6: Client celebrated 50th birthday and 60th anniversary where he financed trips with the sxavingihn the brokerage account therefore in 50th year the end value in brokerage account was deducted for 30,000 on the 60th year the end value was deducted for 95,000 dollars.
Step 7: Total amount of retirement from brokerage account was the sum of the total savings in the brokerage account which was 3227326.653
529 Plan
Step 1: The 529 account had a interest rate of 7.2% compounded daily.Thus the n used was 360. To get the effective annual interest rate we used ((1+(0.072/360))^360)-1= 0.0746476
Step 2: The IRA interest rate has changed to 12% componundede daily thus to get the effective annual interest rate for the IRA account we used ((1+(0.12/360))^360)-1=0.1274743
Step 3: to get the future value of the IRA account we used E=62400*(1.1274743^(22-N)which gave us $139,539
Step 4: To get the Future Value of 529 Account at age 18(Year6)starting of college we used the formula F=10,000*(1.0746476^(8-N) which gave us $16,552.
Step 5: The total amount in the 529 account at the time their daughter starts college is $104,331.
Question 4: From the infroamton given we know that
annual college expenses are running at $35,000 and expected to grow at an annual rate of 3%.
The daughter will be responsible for 35% of her college expenes and are expected to grow at an annual rate of 3%.
Step 1: Annual college expense which is 35000*(1.03)^6= $41,792.
Step 2: To get the amount supported by the work study we have multiplied 41,792*.35=14,627.14 dollars for her first college year.
Step 3: Amount required for first college year is $27,164.69.
Step 4: Total money from when the daughter turned 18 years old from the 529 account is $104,331.
Step 5:The balance amount available from 529 account savings is $77,166. We got that number by subtracting 104331-27164.69.
Step 6: The present value of balance available was calculated by 77166.33/(1.0746476^n)= $77,166.
Thus after completing five years of college the present value of excess amount at year 0 is $19,831. We got this value by summing all the present value available.
The amount available when college is finished is 19831*(1.0746476^5) which gave us $28,423.34.
Yesz, there are sufficient funds available in the 529 account to finance college daughter expense.
Question 5:
Step 1: To calculate the annual college expense for MBA with expected growth we used (1.04^2)*50000= $54080
Step 2: To calculate the client’s help to his daughter’s education the client is subsidizing ⅕ of the fees. Thus 54080*.2= $10816
The father does not have sufficient money.
In addition to their retirement savings, your clients contribute 7% of their quarterly income into a taxable brokerage account that generates an annual after-tax return of 9%, compounded daily, to cover their financial needs before their retirement. Starting this year, your clients commit to help finance their 10-year old daughter’s college education by transfthe brokerage account to a 529 Plan account at the end of each year. Your clients will traerring $10,000 fromnsfer fund annually to the 529 Plan account until their daughter finishes college. The 529 Plan account is expected to generate an annual rate of return of 7.2%, compounded daily.
Besides, your clients plan to celebrate their 50-year birthday anniversary with a European tour that will cost $30,000, and their 60-year birthday anniversary with an around-the-world cruise for a cost of $95,000. They will finance these trips with their savings in the brokerage account.
Any remaining balance in their brokerage account will supplement the SEP IRA account for financing their retirement.
Q2. Determine the cash flows pattern of the quarterly contributions to the brokerage account; and calculate and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Also, calculate the brokerage account balance upon their retirement.
Q3. Determine the cash flows pattern of the annual fund transfers to the 529 Plan account; and calculate and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Also, calculate the 529 Plan account balanceat the time their daughter starts college. Verify your work on the 529 Plan account balance with either the formula or the financial calculator approach!
Today, annual college expenses are running at $35,000, and are expected to grow at an annual rate of 3%. Their daughter, who just turned 10, will enter college when she turns 18, and complete her undergraduate study in FIVE years. Your clients expect their daughter to be responsible for 35% of her college expenses by participating in the Federal Work-Study Program. All annual college expenses will be due at the beginning of each year. Your clients will tap into the 529 Plan account for paying their share of their daughter’s college expenses.
Q4.Will there be sufficient fund in the 529 Plan account to finance their daughter’s college expenses? If not, when will the 529 Plan account run out of money? Support your answer numerically by showing the annual balances of the 529 Plan account through their daughter’s college years.
If there is a positive balance in the 529 Plan account at their daughter’s college graduation, your clients will partially support her graduate study with money left in the 529 Plan account. Their daughter plans to work for FOUR years before returning to graduate school for an MBA. Today, annual expenses for a competitive full-time 2-year MBA program are running at $50,000, and are expected to grow at an annual rate of 4%. Your clients will want to offer assistance to their daughter’s pursuit of graduate education with available fund (if any) in the 529 Plan account by subsidizing one-fifth of the annual expenses during her MBA study.
Q5.Will there be sufficient fund in the 529 Plan account for subsidizing their daughter’s MBA program’s expenses? If not, when will the 529 Plan account run out of money? Support your answer numerically numericallyby showing the annual balances of the 529 Plan account through their daughter’s MBA study.
Upon their retirement, your clients will roll over the entire balance of their SEP IRA account into the Roth IRA account by paying a 25% tax rate on the balance upon conversion. Note that the balance of the brokerage account is on the after-tax basis and hence no further adjustment is needed.
Q6. How large will be the after-tax nest egg upon the retirement of your clients? In other words, calculate the combined balance of the Roth IRA account and their brokerage account as they start enjoying their retirement. Also, offer THREE different recommendationsto your clients that could increase their nest egg.
After their retirement, your clients put their entire nest egg into a conservative account that is expected to generate an annual rate of return of 6%, compounded monthly. In the first month of their retirement, the monthly expenses are expected to be 16% of their quarterly income right before retirement. And the monthly retirement expenses are subjected to monthly inflation at an annual rate of 2.7%. Your clients need to withdraw from this conservative account at the beginning of each month in order to meet the monthly expenses during their retirement horizon of 25 years.
Q7a. Determine the cash flows pattern of the monthly withdrawals from the conservative account; and calculate and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Will your clients be able to leave any inheritance to their daughter at the end of their retirement horizon? If so, calculatethe size of the inheritance. If your clients could not leave any inheritance to their daughter at the end of the retirement horizon, at what age will they run out of money during their retirement? Support your answer numerically. Verify your work on the retirement needs and hence the inheritance with the formula approach!
Q7b. In order to help their daughter manage her finances, your clients instruct you to place the inheritance, i.e., the balance of the conservative account, in a trust fund account at the end of their retirement horizon. The trust fund, which generates an annual rate of return of 7.8%, compounded monthly, distributes fixed semi-annual payments to their daughter and her offspring forever. Determine the cash flows pattern of the semi-annual distributions from the trust fund account; and calculate and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Also, calculate the amount of the semi-annual distribution. Verify your work on the trust fund distributions with the formula approach, and offer THREE different
recommendations (other than those you use in answering Q6 that lead to a larger nest egg) to your clients that could increase their likelihood of leaving an inheritance.
Q7c. Alternatively, your clients decide to gift their daughter annually during their retirement instead of leaving an inheritance at the end of their retirement horizon. Calculate the maximum (fixed) amount of annual gift, and explain precisely your choice of interest rate, i.e., EAR/EPR/PER (select the correct choice), used in the analysis. Verify your work on the annual gifts with the formula or financial calculator approach!
You are required to apply the “two-dimension” approach to TVM analysis in answering the above questions with specific terms for various cash flows patterns that are emphasized in the lecture notes!
