Question 6. Jeff Douglas believes strongly that they should help fully fund the equivalent of a state university education (4 years) for Paul and Marcy. Both sets of grandparents have volunteered to make a lump sum donation (50/50 spilt) to the mutual funds today. In other words, these generous grandparents have stated that they are willing to pool their funds and make a substantial deposit to support an education fund. If today's cost of that type of college education is $25,000 per year and that it will inflate by 4% per year, how much must the grandparents donate to the mutual funds to fully fund these investments (to meet Jeff's goal)? (Assume that Paul and Marcy will start college in 12 and 11 years respectively. You will need to determine the present value of the future college costs. I have put a worksheet at the end of this document that you might find helpful. If you are an Excel user, you can set this up within an Excel worksheet Here are some steps to follow: A. First by growing the cost of education by 4% per year (12 years into the future for Paul and 11 years for Marcy) and then calculating the present value of those future cash flows. Keep in mind that Paul will be going to school for 4 years and so too will Marcy. So, you will need to figure the future value of the cost of education for the first year, second year, third year, and fourth year - each year 4% more costly than the year before! B. Calculate the present value of the future costs of education using the investment yield prediction (8% - see below and notice in the data above that the investment fund has averaged 8% per year). 5 Question 7. Calculate the percentage of net worth represented by the home and the two other largest assets. Consider any loans attributed to those assets so that you show the following - the asset's net value/Total family net worth. Count both cars as one asset (Automobiles). This is called the dominant" asset - other words, if a family is car rich" then that would mean a significant percentage of their wealth (as defined by net worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then we could say that a significant amount of their wealth is from pensions. Question 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the home (between the mortgage and the home equity loan). Question 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their basement. The interest on the loan will be tax deductible. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and Mary should request for their line of credit. Question 10. Jeff and Mary have discussed the need to life insurance and one of their goals is to have a sufficient amount of coverage in the event of an untimely death. Part of the reason for maintaining life insurance coverage is the replacement of earnings and to provide funds for funeral and burial. Jeff's salary is $70,000 per year and Mary makes about $40,000. Funeral and burial expenses average $10,000 a year, according to personal finance articles. The couple also agrees that death benefit proceeds should be sufficient to pay off debts such as the balances owed for credit cards, vacation loans, and home improvement debt. Easy Method - a quick and easy method that assumes the life insurance will cover 70% of 7 years of the deceased person's annual income. DINK method - DINK is an acronym for DINK stands for double income, no kids. Since Jeff and Mary do have two young children, the DINK method's results may not be appropriate however, please calculate AutoSave OFF ES5= Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri v ab Insert v 11 ' X LG Currency FO 1 === E D. DX Delete v PX Paste BI MA V $ % ) EM V Conditional Format Cell Formatting as Table Styles 70 Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B22 x fx E F G H I T J K L L M . N 0 O P R S A B C D 1 College Fund 2 Current cost of 1 year of college $25,000 3 Projected annual inflation of education costs 4% per year 4 Annual return on college investment fund 8% per year 5 6 Paul's projections Years (end Future Value of Education Present value of of year) Cost (4%) Education Costs (using 8% % 7 as the discount rate) 8 12 $40,026 $15,894.80 9 10 131 $41,627 $$15,306.10 11 12 12 14 $43,292 $14,739.21 13 14 15 151 $45,024 $14,193.31 16 17 Total $60,133.42 18 19 20 Marcy's projections Present value of Years (end Future Value of Education of year) Cost (49) Education Costs (using 8% % 21 as the discount rate) 22 111 23 12 13 25 14 26 Total $0.00 27 28 Total Present Value $60,133.42 29 Less amount already invested 30 Total Amount to be Contributed today $60,133.42 31 32 33 34 35 24 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab General TH 28 O FO DX Delete v PX v Paste BI av A. V V F = $ %) to 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A18 X fix A D E F H J L M N o P Q R 21 Total 1 7. Calculate the percentage of net worth represented by the home and the three other largest assets. Consider any loans attributed to those assets so that 2 you show the following - the asset's net value/Total family net worth. If you consider the cars as one of their largest (top 3) assets, count both cars as one 3 asset (Automobiles). The net asset value = Asset Market Value less Related Loan (s). 4 5 This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net 6 worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then 7 8 we could say that a significant amount of their wealth is from pensions. 9 10 11 12 13 14 Net Worth $0 (from the balance sheet) 15 Related Loan(s) Percentage of 16 Asset Asset Value Amount Net Asset Value Net Worth 17 Home 0% 0 18 0% 19 12 0% 20 - 0% 21 $0 $0 $0 0% 22 22 23 22 24 24 25 26 20 2 27 20 28 23 29 30 30 31 22 22 32 22 33 + 34 33 35 30 36 37 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Ready Question 11 + P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab Currency TH Ayu Or DX Delete v PX Paste BI a. A ~ V V E $ %) to 70 Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B14 x fx A C D E G H L M N 0 P Q R S T T F F T J 1 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could 2 possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the 3 home (between the mortgage and the home equity loan). 4 5 6 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their 7 basement. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on 8 any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and 9 Mary should request for their line of credit. 10 11 12 8. Maximum Home Equity Loan 13 14 Value of Home 15 80% of Value 16 Less: Mortgage Balance 17 Maximum Home Equity 18 19 20 9. Disadvantages 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments v 12 Insert v H Calibri (Body) . = = X LG ab Currency FO PH 28 Ow DX Delete v Paste BI U ar A v V V == == $ % ) to20 0 Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 x fx A B D E E F F G H J J K L M N O Jeff Mary $0 $0 $0 $0 John $10,000 Mary $10,000 $10,000 $10,000 $10,000 $10,000 Jeff Mary 1 2 EASY Method 3 Current Income (salaries) ( 4 4 Insurance need 5 Current Life Insurance Coverage 6 Additional Life Insurance Needed (EASY METHOD) 7 8 DINK Method = 9 Funeral Expenses 10 One-half of mortgage 11 One-half of auto loan - 12 One-half of credit card balance - Other Debts 13 Other Debts 14 Insurance Needed 15 Current Life Insurance Coverage 16 Additional Life Insurance Needed (DINK Method) 17 18 Family Need Method 19 What They Have Now: : 20 Bank Accounts (Checking) Investments, Savings, Portion of Deceased's Retirement Account 21 (not including home) 22 Life Life Insurance Policies (Death Benefit) 23 Total 24 What Dependents Need 2 25 Funeral Expenses 26 Mortgage and Loans 27 Credit Card Debt 28 Subtotal 29 Living Expenses 75% of family income x Number of Years dependents will need it 30 (assume 15 years) 31 Emergency Fund 32 Total of what the dependents will need 33 Additional Life Insurance Needed (Family Need Method) 34 35 Most Coverage required from the three methods 36 $0 $0 $10,000 $10,000 $10,000 $10,000 $ $0 $10,000 $0 $10,000 $10,000 $10,000 What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Ready Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + P 100% Question 6. Jeff Douglas believes strongly that they should help fully fund the equivalent of a state university education (4 years) for Paul and Marcy. Both sets of grandparents have volunteered to make a lump sum donation (50/50 spilt) to the mutual funds today. In other words, these generous grandparents have stated that they are willing to pool their funds and make a substantial deposit to support an education fund. If today's cost of that type of college education is $25,000 per year and that it will inflate by 4% per year, how much must the grandparents donate to the mutual funds to fully fund these investments (to meet Jeff's goal)? (Assume that Paul and Marcy will start college in 12 and 11 years respectively. You will need to determine the present value of the future college costs. I have put a worksheet at the end of this document that you might find helpful. If you are an Excel user, you can set this up within an Excel worksheet Here are some steps to follow: A. First by growing the cost of education by 4% per year (12 years into the future for Paul and 11 years for Marcy) and then calculating the present value of those future cash flows. Keep in mind that Paul will be going to school for 4 years and so too will Marcy. So, you will need to figure the future value of the cost of education for the first year, second year, third year, and fourth year - each year 4% more costly than the year before! B. Calculate the present value of the future costs of education using the investment yield prediction (8% - see below and notice in the data above that the investment fund has averaged 8% per year). 5 Question 7. Calculate the percentage of net worth represented by the home and the two other largest assets. Consider any loans attributed to those assets so that you show the following - the asset's net value/Total family net worth. Count both cars as one asset (Automobiles). This is called the dominant" asset - other words, if a family is car rich" then that would mean a significant percentage of their wealth (as defined by net worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then we could say that a significant amount of their wealth is from pensions. Question 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the home (between the mortgage and the home equity loan). Question 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their basement. The interest on the loan will be tax deductible. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and Mary should request for their line of credit. Question 10. Jeff and Mary have discussed the need to life insurance and one of their goals is to have a sufficient amount of coverage in the event of an untimely death. Part of the reason for maintaining life insurance coverage is the replacement of earnings and to provide funds for funeral and burial. Jeff's salary is $70,000 per year and Mary makes about $40,000. Funeral and burial expenses average $10,000 a year, according to personal finance articles. The couple also agrees that death benefit proceeds should be sufficient to pay off debts such as the balances owed for credit cards, vacation loans, and home improvement debt. Easy Method - a quick and easy method that assumes the life insurance will cover 70% of 7 years of the deceased person's annual income. DINK method - DINK is an acronym for DINK stands for double income, no kids. Since Jeff and Mary do have two young children, the DINK method's results may not be appropriate however, please calculate AutoSave OFF ES5= Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri v ab Insert v 11 ' X LG Currency FO 1 === E D. DX Delete v PX Paste BI MA V $ % ) EM V Conditional Format Cell Formatting as Table Styles 70 Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B22 x fx E F G H I T J K L L M . N 0 O P R S A B C D 1 College Fund 2 Current cost of 1 year of college $25,000 3 Projected annual inflation of education costs 4% per year 4 Annual return on college investment fund 8% per year 5 6 Paul's projections Years (end Future Value of Education Present value of of year) Cost (4%) Education Costs (using 8% % 7 as the discount rate) 8 12 $40,026 $15,894.80 9 10 131 $41,627 $$15,306.10 11 12 12 14 $43,292 $14,739.21 13 14 15 151 $45,024 $14,193.31 16 17 Total $60,133.42 18 19 20 Marcy's projections Present value of Years (end Future Value of Education of year) Cost (49) Education Costs (using 8% % 21 as the discount rate) 22 111 23 12 13 25 14 26 Total $0.00 27 28 Total Present Value $60,133.42 29 Less amount already invested 30 Total Amount to be Contributed today $60,133.42 31 32 33 34 35 24 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab General TH 28 O FO DX Delete v PX v Paste BI av A. V V F = $ %) to 70 Conditional Format Cell Formatting as Table Styles Format Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF A18 X fix A D E F H J L M N o P Q R 21 Total 1 7. Calculate the percentage of net worth represented by the home and the three other largest assets. Consider any loans attributed to those assets so that 2 you show the following - the asset's net value/Total family net worth. If you consider the cars as one of their largest (top 3) assets, count both cars as one 3 asset (Automobiles). The net asset value = Asset Market Value less Related Loan (s). 4 5 This is called the "dominant" asset - other words, if a family is "car rich" then that would mean a significant percentage of their wealth (as defined by net 6 worth) would be from the value of its cars. The cars would be the dominant asset. If the family's net worth is mostly from their retirement accounts, then 7 8 we could say that a significant amount of their wealth is from pensions. 9 10 11 12 13 14 Net Worth $0 (from the balance sheet) 15 Related Loan(s) Percentage of 16 Asset Asset Value Amount Net Asset Value Net Worth 17 Home 0% 0 18 0% 19 12 0% 20 - 0% 21 $0 $0 $0 0% 22 22 23 22 24 24 25 26 20 2 27 20 28 23 29 30 30 31 22 22 32 22 33 + 34 33 35 30 36 37 37 38 39 40 Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Ready Question 11 + P 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments Calibri (Body) v 12 Insert v . = = X LG ab Currency TH Ayu Or DX Delete v PX Paste BI a. A ~ V V E $ %) to 70 Conditional Format Cell Formatting as Table Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B14 x fx A C D E G H L M N 0 P Q R S T T F F T J 1 8. Jeff is considering applying for a home equity loan to finance the basement project. What is the maximum home equity loan the Douglas' could 2 possibly get based only on equity (and ignoring cash flow considerations)? Assume that a bank is willing to loan up to 80% of the value of the 3 home (between the mortgage and the home equity loan). 4 5 6 9. Home equity loans have many advantages. For example, the home equity loan may be the source of funds to help Jeff and Mary finish their 7 basement. The arrangement that Jeff and Mary are looking at will involve a 15-year payback period and would allow for them to draw down on 8 any unused funds in a credit line arrangement. Please describe two disadvantages of a home equity loan and recommend an amount that Jeff and 9 Mary should request for their line of credit. 10 11 12 8. Maximum Home Equity Loan 13 14 Value of Home 15 80% of Value 16 Less: Mortgage Balance 17 Maximum Home Equity 18 19 20 9. Disadvantages 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Q1 Personal Balance Sheet Questions 2, 3, 84 Question 5 Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + Ready 100% AutoSave OFF DES 5- Douglas Case(2) Q Home Insert Draw Page Layout Formulas Data Review View Acrobat Tell me Share Comments v 12 Insert v H Calibri (Body) . = = X LG ab Currency FO PH 28 Ow DX Delete v Paste BI U ar A v V V == == $ % ) to20 0 Conditional Format Formatting as Table Cell Styles Format v Sort & Filter Find & Select Analyze Data Create and Share Adobe PDF B3 x fx A B D E E F F G H J J K L M N O Jeff Mary $0 $0 $0 $0 John $10,000 Mary $10,000 $10,000 $10,000 $10,000 $10,000 Jeff Mary 1 2 EASY Method 3 Current Income (salaries) ( 4 4 Insurance need 5 Current Life Insurance Coverage 6 Additional Life Insurance Needed (EASY METHOD) 7 8 DINK Method = 9 Funeral Expenses 10 One-half of mortgage 11 One-half of auto loan - 12 One-half of credit card balance - Other Debts 13 Other Debts 14 Insurance Needed 15 Current Life Insurance Coverage 16 Additional Life Insurance Needed (DINK Method) 17 18 Family Need Method 19 What They Have Now: : 20 Bank Accounts (Checking) Investments, Savings, Portion of Deceased's Retirement Account 21 (not including home) 22 Life Life Insurance Policies (Death Benefit) 23 Total 24 What Dependents Need 2 25 Funeral Expenses 26 Mortgage and Loans 27 Credit Card Debt 28 Subtotal 29 Living Expenses 75% of family income x Number of Years dependents will need it 30 (assume 15 years) 31 Emergency Fund 32 Total of what the dependents will need 33 Additional Life Insurance Needed (Family Need Method) 34 35 Most Coverage required from the three methods 36 $0 $0 $10,000 $10,000 $10,000 $10,000 $ $0 $10,000 $0 $10,000 $10,000 $10,000 What do you recommend for death benefit amounts for each person (Jeff and Mary)? What is Q1 Personal Balance Sheet Questions 2, 3, &4 Question 5 Ready Question 6 Question 7 Question 8 and 9 Question 10 Question 11 + P 100%