hw 12 financial calculation

.xlsx

School

University of Utah *

*We aren’t endorsed by this school

Course

4020

Subject

Electrical Engineering

Date

Dec 6, 2023

Type

xlsx

Pages

Uploaded by BarristerAnt10509

You are a Certified Financial Planner. You have been hired by Mr. and Mrs. Smith to help them review their financ and plan for retirement. The Smiths have provided you the following information: (There are 3 areas where they A) Retirement Analysis a Both Mr. and Mrs. Smith are 25 years old b They want to retire when they are 65 years old (40 years) c They do not expect to receive any of their retirement from Social Security d They expect inflation to average 3% over their remaining life expectancy e They have a moderate tolerance for risk f The Smiths are both CPAs and have their own CPA firm with no employees now and do not expect any g Their main investment is a SEP IRA which limits their total investment per year to $51,000 h The financial institution they have chosen offers the following investment options for their SEP IRA 1 Year 3 year 5 Year Investments Return Return Return Precious Metals and Mining Fund 22.69% -14.39% 2.58% Real estate fund 13.38% 11.85% 23.84% Prime Money Mkt Fund 0.02% 0.03% 0.05% High Yield Bond Fund 10.25% 8.65% 11.91% Conservative Bond Fund 4.15% 3.49% 4.63% International Stock Fund 21.93% 5.88% 11.40% Small Cap Stock Fund 26.54% 16.17% 22.28% Blue Chip Stock Fund 29.11% 16.29% 18.16% I They expect to live 40 years after they retire (105 years old) j They have calculated that they need a minimum of 15,000 a month to live on through retirement (but k The average interest rate after retirement is 5% l They want to leave their Kids $3,000,000 when they die j They want to structure their investments to return a minimum of 5% annually 1 What is the minimum amount that the Smiths must save by retirement to retire at 65? Arguments Amount i 5.00% <- Enter your arguments here and referenc N 40 (use the help function to determine the req NPER 12 PMT 15,000 FV 3,000,000 END OF PERIOD 0 Required savings at retirement (3,518,461)

2 How much will they need to invest each month using the minimum acceptable rate of return (5%) to Arguments Amount I 5.00% <- Enter your arguments here and referenc N 40 (use the help function to determine the req NPER 12 FV (3,518,461) PV 0 END. OF PERIOD 0 Monthly required investment (2,306) 3 Does this amount exceed the SEP IRA Max? Total amount per year (27,667.74) Monthly required investment x 12 SEP IRA Max 51,000 Does it exceed the Max? NO 4 IF the amount is below the Max how much would they have at retirement, if they invest the max? Arguments Amount N 40 <- Enter your arguments here and referenc I 5% (use the help function to determine the req NPER 12 PMT (4,250) (The $51,000 should be divided by 12 beca PV 0 END OF PERIOD 0 Amount expected at retirement 6,485,586 5 The Smiths want to invest the minimum monthly amount but maximize their retirement within thei investment mix (fund target allocation) that would likely give them a premium but not exceed their Below are the guidelines the Smith have given you: - They want their investments to be diversified as follows: o 10% in cash (no more or less) o Minimum of 5% in each fund o No more than 25% in any one fund o The initial payment should be allocated in the same percentage as the fund target allocation. Pay o The fund manager has been instructed to re-allocate the investment annually to meet the fund ta - The minimum acceptable risk adjusted return of the total portfolio is 5% - The total risk on their portfolio cannot exceed a risk level of 3 Fill out the tables below and play with the investment mix until you have maximized the investment a In this how m allocat investm

Monthly Payment into IRA (2,306) <- From 2 above Number of Years 40 <- Fill these out from information about the Number of Payments Per Year 12 Expected Return (rate) after retirement 5% Balance remaining at Death 3,000,000 Funds Precious Metals and Mining Fund 5% ($115.28) 4.66% Real estate fund 5% ($115.28) 13.95% High Yield Bond Fund 20% ($461.13) 9.39% Conservative Bond Fund 25% ($576.41) 4.70% International Stock Fund 5% ($115.28) 12.44% Small Cap Stock Fund 5% ($115.28) 16.93% Blue Chip Stock Fund 25% ($576.41) 16.25% Prime Money Mkt Fund 10% ($230.56) 1.45% Total 100% (2,306) 9.7% Targets 100% Difference 0% A This is the allocation percentage you assign to each investment. The total must equal to 100%. B C Use a VLOOKUP to pull this information from the financial institutions table of available investments a D The risk adjusted rate of return is the target rate of return multiplied by the chance of making the targ Use a VLOOKUP to pick up the chance of making the target from the financial institutions table above. E Use an array formula to calculate the weighted average total for each column. F G H I Calculate the Smith's monthly retirement income using the payment function. J Enter the targets from the information above (the risk free amount at retirement target was calculated B) Rental Unit The smiths are thinking about buying a rental unit that has the following cash flows. They want to kn Cost 250,000 Monthly Rent (2% annual increase) 1,600 $1,000 Annual appreciation 3% The next best investment returns 10% 10% Target Allocation Allocation of Year 1 Payment Target Rate of Return Put a formula in these cells that allocates the payment by the percentages you put in A . Calculate the future value of the portfolio using the weighted average target rate of return. Calculate the future value of the portfolio using the weighted average risk adjusted rate of return Allocate the total ( F & G above) by the target allocation for each fund ( A ). Annual Expenses (2% inflation) A B C E

5 yrs. Calculate the NPV and IRR Ignore tax Value of the house in five years? $289,818.52 < use a future value calculation Investment $ (250,000) Year 1 $ 18,200 Year 2 $ 18,564 Year 3 $ 18,935 Year 4 $ 19,314 Year 5 $ 309,519 NPV at 10% $1,492 IRR 10.15% Should they make this investment? YES C) Cabin Mrs. Smith inherited a cabin from her parents plus $100,000 in cash. The cabin needs a complete rem They rent the cabin out when they are not using it. Rent is collected by a management firm, netted ag the Smith's semi annually. The Smiths want to use their inheritance to fund their children's education. cabin now in its current condition for $50,000 (after taxes) and invest the proceeds and the $100,000 the improvements to the cabin and wait to sell it when her children are ready to go to school. She wou the improvements and wait so the family can use the cabin as long as the proceeds from selling the ca investing proceeds. See cash flows below. Calculate the following Net Present Value at 5% 102,770 Net Present Value at 10% (29,630) IRR 8.4453% Should she sell it now or later and why? She should sell now. If she invests $150,00 If she could earn 10% on the investment by selling I would recommend her to wait. At 10% an now what would you recommend? What is the interest rate she would have to earn 8.5% in order to make it better to sell now? Expected Cash Flows: They plan on keeping the property for five years then sell it. The home is there are no the FV calcul

Remodel 1/1/2015 (150,000) < the $100,000 Pay insurance & property Tax 1/26/2015 (2,500) Rent Payment 6/30/2015 5,000 Rent Payment 12/27/2015 5,000 pay insurance & property Tax 1/26/2016 (2,550) Rent Payment 6/30/2016 5,100 Rent Payment 12/27/2016 5,100 pay insurance & property Tax 1/26/2017 (2,601) Rent Payment 6/30/2017 5,202 Rent Payment 12/27/2017 5,202 pay insurance & property Tax 1/26/2018 (2,653) Rent Payment 6/30/2018 5,306 Rent Payment 12/27/2018 5,306 pay insurance & property Tax 1/26/2019 (2,706) Rent Payment 6/30/2019 5,412 Replace the well 7/25/2019 (30,000) Rent Payment 12/27/2019 5,412 pay insurance & property Tax 1/26/2020 (2,760) Rent Payment 6/30/2020 5,520 Rent Payment 12/27/2020 5,520 pay insurance & property Tax 1/26/2021 (2,815) Rent Payment 6/30/2021 5,631 Put on a new roof 8/14/2021 (10,000) Rent Payment 12/27/2021 5,631 pay insurance & property Tax 1/26/2022 (2,872) Rent Payment 6/30/2022 5,743 Rent Payment 12/27/2022 5,743 pay insurance & property Tax 1/26/2023 (2,929) Rent Payment 6/30/2023 5,858 Rent Payment 12/27/2023 5,858 pay insurance & property Tax 1/26/2024 (2,988) Rent Payment 6/30/2024 5,975 Rent Payment 12/27/2024 5,975 pay insurance & property Tax 1/26/2025 (3,047) Rent Payment 6/30/2025 6,095 Rent Payment 12/27/2025 6,095 pay insurance & property Tax 1/26/2026 (3,108) Rent Payment 6/30/2026 6,217 Rent Payment 12/27/2026 6,217 pay insurance & property Tax 1/26/2027 (3,171) Rent Payment 6/30/2027 6,341 Rent Payment 12/27/2027 6,341 pay insurance & property Tax 1/26/2028 (3,234) Rent Payment 6/30/2028 6,468 Rent Payment 12/27/2028 6,468

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version