Share
Explore BrainMass

Prepayment versus investment calculator exercise

The data you will need for the prepayment scenario include the following.

Loan Balance: $135000
Current Payment: $990.62
Additional Payment: $50.00
Loan Interest Rate: 8.0%
Loan Interest Deductibility: YES
Investment Rate Return: 6.00%*
Tax Bracket: 30.00%
Investment Type: After-Tax

*The Investment rate return is your opportunity cost estimate. It is the annual rate you think you can earn on the $50 extra principal payment if you did not make extra principal payments on your mortgage but instead, invested it.

Now visit the website http://www.interest.com/hugh/calc, and select Prepayment vs. Investment.

A.After 12 months of making extra payments, what will be the loan balance?

B. .After 12 months of making the regular payment and investing the $50, what will be the loan balance?

C. Under the regular payment and investing option, excluding the tax due on the interest earned, what is the investment balance after 12 months?

D.Compare the scenarios of investment versus prepayment by examining the 60th payment, which occurs at the end of the fifth year. What is the difference between the (a) interest portion of that payment, (b) tax deduction for interest, and (c) principal balance? Finally, how much is in the investment account?

E.(a) How long does it take to repay the entire loan under the prepayment option? (b) What is the total interest paid over the life of the loan?

F.Compare the total interest paid under each scenario? How much less in interest do you pay under the prepayment option?

G.If you make an extra $50.00 principal payment per month, what are the opportunity cost considerations?

H. What are the relevant cash flows to consider in this decision? For example, do you consider the tax implications and if so, then how?

I. Do you go out to lunch too often? Use Hugh's Lunch Savings Calculator to see how much money you can save by not going out to lunch. Suppose you usually spend $6.00 a day when you go out to lunch, when bringing your lunch to school/work would only cost you about $2.00 a day. Since there are approximately 250 weekdays in a year, enter that value for the days eaten per year. How much money would you save after 15 years if you could earn a 10% yield on the money you save?

J. Suppose your investment account earns an average annual return of 9%, and the average rate of inflation is 3%. Using Hugh's "What's a Million" calculator, how long would it take to have a million dollars, if you started with an initial investment of $20,000and made monthly $150 contributions (assume that your deposits are inflated at the average rate of inflation)?

Solution Preview

See the attached file for complete solution. The text here may not be copied exactly as some of the symbols / tables may not print. Thanks

The data you will need for the prepayment scenario include the following.

Loan Balance: $135000
Current Payment: $990.62
Additional Payment: $50.00
Loan Interest Rate: 8.0%
Loan Interest Deductibility: YES
Investment Rate Return: 6.00%*
Tax Bracket: 30.00%
Investment Type: After-Tax

*The Investment rate return is your opportunity cost estimate. It is the annual rate you think you can earn on the $50 extra principal payment if you did not make extra principal payments on your mortgage but instead, invested it.

Now visit the website http://www.interest.com/hugh/calc, and select Prepayment vs. Investment.

A.After 12 months of making extra payments, what will be the loan balance?
See output below:
The loan balance is $133,249.29

B. .After 12 months of making the regular payment and investing the $50, what will be the loan balance?
See output below:
The loan balance is $133,871.79

C. Under the regular payment and investing option, excluding the tax due on the interest earned, what is the investment balance after 12 months?
See output below:
The investment balance is $623.64

D.Compare the scenarios of investment versus prepayment by examining the 60th payment, which occurs at the end of the fifth year. What is the difference between the (a) interest portion of that payment, (b) tax deduction for interest, and (c) principal balance? Finally, how much is in the investment account?
See output below:
The investment balance is $623.64
a) Interest payment under prepayment is 832.51 while under investment is 856.50
b) Tax deduction for Interest payment under prepayment is 249.75 while under investment is 256.95
c) Principle balance under prepayment is 1242,667.68 while under investment is 128,341.53
d) Investment account is 3,602.75 and tax due on investment account is 57.61
E.(a) How long does it take to repay the entire loan under the prepayment option? (b) What is the total interest paid over the life of the loan?
a) Loan payback period under prepay is 25.083 years or 301 months.
b) Total interest paid = 178,461.11

F.Compare the total interest paid under each scenario? How much less in interest do you pay under the prepayment option?
Total interest paid under investment plan is 221,560.64. So under investment plan the excess interest paid is 221,560.64-178,461.11=43,099.53

G.If you make an extra $50.00 principal payment per month, what are the opportunity cost considerations?
Opportunity cost is the return which can be earned on the investments.

H. What are the relevant cash flows to consider in this decision? For example, do you consider the tax implications and if so, then how?
The relevant cash flows are given below. The sign of the cash flow is indicated in brackets.
Repayment of extra principle (Negative)
Interest savings on mortgage due to additional principle repayment (Positive)
Reduction in tax benefits on interest paid on mortgage (Negative)
Opportunity cost (return lost on investment) (Negative)
Tax payments on income earned from investments under investment option (Positive)

OUTPUT

Loan Balance 135,000.00
Current Payment 990.62
Additional Payment 50.00
Loan Interest Rate 8.00 %
Loan Interest Deductibilty Yes
Investment Return Rate 6.00 %
Tax Bracket 30.00 %
Investment Type After-Tax

PREPAYING INVESTING
Month Loan Bal (P) Int Paid (P) Tax Ded (P) Loan Bal (I) Int Paid (I) Tax Ded (I) Inv Bal (I)
1 134,859.38 900.00 270.00 134,909.38 900.00 270.00 50.00
2 134,717.82 899.06 269.72 134,818.16 899.40 269.82 100.35
3 134,575.32 898.12 269.44 134,726.32 898.79 269.64 151.05
4 134,431.87 897.17 269.15 134,633.88 898.18 269.45 202.11
5 134,287.46 896.21 268.86 134,540.82 897.56 269.27 253.52
6 134,142.09 895.25 268.57 134,447.14 896.94 269.08 305.30
7 133,995.75 894.28 268.28 134,352.83 896.31 268.89 357.44
8 133,848.44 893.31 267.99 134,257.90 895.69 268.71 409.94
9 133,700.14 892.32 267.70 134,162.33 895.05 268.52 462.81
10 133,550.86 891.33 267.40 134,066.13 894.42 268.32 516.04
11 133,400.57 890.34 267.10 133,969.28 893.77 268.13 569.65
12 133,249.29 889.34 266.80 133,871.79 893.13 267.94 623.64
TAX - - - - - - 6.00
13 133,097.00 888.33 266.50 133,773.65 892.48 267.74 672.00
14 132,943.69 887.31 266.19 133,674.85 891.82 267.55 726.71
15 132,789.36 886.29 265.89 133,575.40 891.17 267.35 781.81
16 132,634.01 885.26 265.58 133,475.28 890.50 267.15 837.29
17 132,477.61 884.23 265.27 133,374.49 889.84 266.95 893.16
18 132,320.18 883.18 264.96 133,273.04 889.16 266.75 949.42
19 132,161.69 882.13 264.64 133,170.90 888.49 266.55 1,006.07
20 132,002.15 881.08 264.32 133,068.09 887.81 266.34 1,063.12
21 131,841.54 880.01 264.00 132,964.59 887.12 266.14 1,120.57
22 131,679.87 878.94 263.68 132,860.40 886.43 265.93 1,178.42
23 131,517.11 877.87 263.36 132,755.52 885.74 265.72 1,236.67
24 131,353.27 876.78 263.03 132,649.93 885.04 265.51 1,295.33
TAX - - - - - - 17.64
25 131,188.34 875.69 262.71 132,543.65 884.33 265.30 1,336.76
26 131,022.31 874.59 262.38 132,436.65 883.62 265.09 1,396.15
27 130,855.17 873.48 262.04 132,328.94 882.91 264.87 1,455.96
28 130,686.92 872.37 261.71 132,220.52 882.19 264.66 1,516.19
29 130,517.55 871.25 261.37 132,111.37 881.47 264.44 1,576.84
30 130,347.05 870.12 261.04 132,001.49 880.74 264.22 1,637.91
31 130,175.41 868.98 260.69 131,890.88 880.01 264.00 1,699.41
32 130,002.62 867.84 260.35 131,779.53 879.27 263.78 1,761.34
33 129,828.69 866.68 260.01 131,667.44 878.53 263.56 1,823.70
34 129,653.59 865.52 259.66 131,554.60 877.78 263.33 1,886.49
35 129,477.33 864.36 259.31 131,441.01 877.03 263.11 1,949.73
36 129,299.89 863.18 258.95 131,326.67 876.27 262.88 2,013.40
TAX - - - - - - 30.08
37 129,121.27 862.00 258.60 131,211.56 875.51 262.65 2,047.44
38 128,941.46 860.81 258.24 131,095.68 874.74 262.42 2,111.86
39 128,760.45 859.61 257.88 130,979.03 873.97 262.19 2,176.73
40 128,578.23 858.40 257.52 130,861.61 873.19 261.96 2,242.05
41 128,394.80 857.19 257.16 130,743.40 872.41 261.72 2,307.83
42 128,210.14 855.97 256.79 130,624.40 871.62 261.49 2,374.06
43 128,024.26 854.73 256.42 130,504.61 870.83 261.25 2,440.76
44 127,837.13 853.50 256.05 130,384.02 870.03 261.01 2,507.93
45 127,648.76 852.25 255.67 130,262.63 869.23 260.77 2,575.56
46 127,459.13 850.99 255.30 130,140.43 868.42 260.53 2,643.67
47 127,268.24 849.73 254.92 130,017.41 867.60 260.28 2,712.25
48 127,076.07 848.45 254.54 129,893.57 866.78 260.03 2,781.31
TAX - - - - - - 43.38
49 126,882.63 847.17 254.15 129,768.91 865.96 ...

Solution Summary

This post compares and explains various concepts involved with prepayment versus investment

$2.19