70.2K

Verified Solution

Link Copied!

A) Create an amortization table for a business with the following information.

Loan: $3,000,000

Annual interest rate: 3.2%

Loan period: 5 years

Payments: monthly, end of each month

Loan will be fully paid in 5 years.

B) Create an amortization table to show Interest, Principal and Balance. Next, figure cumulative interest payments and cumulative principal payments of this loan for each year.

C) Build a cash flow table for a service business using the loan information from part B. Calculate net cash flow for the first 5 years. Additional information is as follows:

• Facility lifetime: 30 years
• Investment on the facility and equipment: $3,000,000
• Salvage value: $1,000,000
• Annual revenue: $650,000 first year, then $1,200,000 every year
• Annual operating costs:
  • Insurance: $13,000
  • Maintenance and utility: $18,000
  • Wages: $240,000
• Income tax rate: fixed at 24%.

I already done part A and B

E H 1 K L M N 0 Q R Cumulative principal & interest payments: 1 4 Payments in year interest Principal 2 3 5 S87,900.0) ($69,341.70) SS0.489.72) ($31.225.48) ($11,129.171 ($562,276,54) 15530,535.64 $599,387.68) (5618,85192) 15638.948.221 A B D 1 2 2 Loan 3,000,000.00 3 APR 3.20% 4 4 Term 5 5 Parenyar 12 6 7 Paytren I$54,173.12) 8 9 Amortization table: 10 Period Interest Principal Balance 11 1 ($8,000.00) ($ 46,173.12) 2,953,826.88 12 2 I$ 7,876.871 (546,296.24 2,907,530.54 13 3 I$ 7,753.421 ($46,419.701 2.B6 1,110.94 4 $7,629.63) ( $46,543.49 2,814,567.45 15 5 (57,505.51) ($46,667.60) 2,767,899.85 06 G ($7,381.071 ($46,792.05 2.721.107.80 17 ? ($ 7,256.29) ($ 46,916.83 2,674,190.97 8 157,131.18) ($ 47,041.941 7,527,149.03 19 9 ( I$ 7,005.731 ($47,167.39) 2,579,98 1.54 20 10 ($6.879.95) ($ 47,293.17) 2,532.698.49 21 11 ($ 6,753.84) ($47,419.28 2,485,269.20 22 12 1$6,627.38) $ ($47,545.73) 2,437,723.46 23 13 ($6.5CO.GO) ($ 47,672.52) 2.390.050.94 241 14 ($ 6,373.42) (547,799.65) 2,342,251.30 55 15 156,246.001 (547,927.11) 2,294,324.18 25 16 1$6,118.20 ( ($ 48,054.92) 2.246,269.26 17 $5,990.05) (S48,183.07) 2,198,096.20 28 0 18 ($ 1.561 (548,311.55) 2.149,774.65 39 19 ($5.722.73) ($48,440.30 2,101,334.26 20 ($5,603.5G) ($48,569.55 2.052,764.70 91 21 $ 104) $48,699.00 2,004,065.63 32 22 $5,344.18) ($48,828.941 1,955,236.58 a3 23 $5,213.56) ($ 48,959.15 1,906,277.52 24 $5,083.41) (549,039.71 1.957.197.92 35 25 184.952.501 ($ 49,220.62) 1,807,967 21 35 26 103.02 ($4,821.25) ($ 49,351,87) 1,758,615.14 97 27 1$4,629.641 I) ($49,453.45 1.709.12 1.8G 28 20 ($4.557.68) ($ 49.615.43) 1.659,516.43 29 (54,425,38) ($ 49,747.741 1,509,768,69 20 30 1$4,292.72 ($49,890.40 1.559.888.29 1 31 ($4.159.701 ($50,013.41) 1,509,874.99 12 32 1$ 4,026.33) ($50,146.78 1,459,728.09 23 33 $3,892.61) ($50,280.51) 1,409,447.58 19.7 1.359.032.99 Amortization Cash flow + S Ready HHH + 100% A C n F E H 1 K M R 30 2491 240,000 3,000,000 13,000 18,000 1,000,000 1 Year 3 1200000.00 5 2 1200000.00 4 1200000.00 GSC000.00 1200000.00 1 2 Life 3 Income Tax Rate 4 Wages 5 Property Value 6 Insurance 7 Maintenance and utility 2 Salvage 9 10 11 12 13 14 Revenue 15 Operating costs 16 17 Operating income 18 19 Interest Payments 20 Depreciation 21 22 Table income 23 24 Tax 25 After Tax income 26 Add depreciation 27 Principal Payments 28 29 Net Cash Flow 30 31 32 33 34 36 37 39 40 41 12 44 Amortization Cash flow + Ready DP 100%

Answer & Explanation Solved by verified expert

See Answer