Joe Byrnes is the founder of CorpTech, a startup information technology company located in Seattle, Washington. Joe has lined up financial backing needed to start CorpTech. The company is borrowing $550,000 dollars at 4.5% annual interest to be repaid, quarterly, in 5 years. In addition to the loan, CorpTech is purchasing new equipment. Assist Joe in calculating borrowing costs by creating a loan schedule and amortization table. Also include the Cumulative Interest and Principal Payments. The accounting department also needs the Depreciation factor on the new equipment. Completing the following: 1. Open the CorpTech data file and save it as XX_CorpCosts, substituting the XX with your initials. 2. Loan Schedule In the loan worksheet, enter the loan schedule Loan (PV) $550,000 information shown on the right in the range of Annual Rate B4:B6, and B8. Payment per Year Rate Per Perod (Rate) 3. In cell B7, calculate the Period Rate (Rate). Years 5 4. In cell B9, calculate the Payments (NPER). Payments (NPER) Payment (PMT) 5. In cell B10, calculate the quarterly Payment (PMT). Show as a positive. 6. In cell C16, using a reference formula, return the Loan (PV) amount. 7. In cell D16, using the IPMT function, calculate the Interest Payment. 4.5% 4 8. In cell E16, using the PPMT function, calculate the Principal Payment. In cell F16, calculate the Total Payment by adding the Interest Payment to the Principal 9. Payment. 10. In cell C17, add the starting principal balance in cell C16 to the principal payment in cell E16. 11. Copy D16:F16 and paste it to D17:F17. 12. Select C17:F17 and auto fill to F35. 13. In cell C36, calculate the final balance owed by adding the final Remaining Principal payment to the last Principal Payment. Verify the balance is 0. 14. In cell B43, using the CUMIPMT function, calculate the cumulative interest. Note: payments will be made at the start of the period. 15. In cell 044, using the CUMPRINC function, calculate the cumulative principal. 16. Select B43:B44 and auto fill to F44. 17. Select G43:G44 and auto sum. 18. In cell B45, calculate the Principal Remaining for Year 1 by adding the Year 1 Principal to the Loan (PV) amount. Make sure the order in your formula is correct! 19. In cell C45, calculate the Principal Remaining for Year 2 by adding the Year 1 Principal Remaining balance (B45) to Year 2 Principal (C44). Auto fill the formula in cell 045 to F45. Note: the balance in cell F45 should be 0. 20. Click the Equipment Sheet. Enter the following Information in the range B4:B6: Depreciation Factor $ Current Price (Cost) Salvage Value (Salvage) Life of Asset (Life) $ 125,000 20,000 10 21. In cell E5, using the SLN function, calculate the Straight-Line Depreciation. Copy the formula in cell E5 and paste it to E6:E9. 22. In cell F5, using a formula, refer to the Year 1, Yearly Depreciation in cell E5 to display the depreciation for the first year. 23. Calculate the Cumulative Depreciation in cells F6:F9. (Hint: in cell F6, add Year 1 Depreciation to the Year 1 Cumulative Depreciation. Auto fill the formula F6 through F9.) 24. In G5, calculate the Depreciated Asset Value by subtracting the Cumulative Depreciation from the Current Price. Copy the formula to cells G6:49. Compare your results with the screen print Straight-Line (SLN) to the right. Yearly Cumulative Depreciated Year Deprecation Deprecation Asset Value 1 10,500 10,500 $ 114,500 21 10,500 21,000 $ 104,000 3 10,500 31,500 $ 93,500 10,500 42,000 $ 83,000 51 10,500 52,500 $ 72,500 4 25. In cell J5, using the DB function, calculate the Declining Balance Depreciation. Auto fill the formula through 19. 26. In cell K5, using a formula, refer to the Year 1 Yearly Depreciation in cell J5 to display the Depreciation for the first year. 27. Calculate the Cumulative Depreciation in cells K6:K9. (Hint: in cell K6, add Year 1 Cumulative Depreciation to Year 2 Yearly Depreciation. Auto fill the formula through K9.) 28. In 15, calculate the Depreciated Asset Value by subtracting the Cumulative Depreciation from Year the Current Price. Copy the formula to cells 1 Declining Balance (DB) Yearly Cumulative Depreciated Deprecation Deprecation Asset Value 20,875 20,875$ 104,125 17,389 38,264 $ 86,736 14,485 52,749 $ 72,251 12,066 64,815$ 60,185 10,051 74,866 $ 50,134 L6:L9. Compare your results with the screen print 2 3 4 to the right. 51 29. Save and upload the file to D2L, Assignments. G A B 51 4 16 52 5 17 53 5 18 54 5 19 55 5 20 36 Final Balance 57 58 39 Cumulative Interest and Principal Payments per 40 Year 1 41 1 C $149,242.60 $120,058.16 $90,545.39 $60,700.61 $30,520.07 $0.00 D E F ($1,678.98 ($29,184.44) (530,863.42) ($1,350.65 (529,512.77) ($30,863.42) ($1,018.64) ($29,844.79 (530,863.42 (5682.88) $30,180.54) (530,863.42 ($343.35) (530,520.07) (530,863.42 Year 3 Year 2 5 Year 4 13 Year 5 17 9 Quarters 4 8 12 42 16 20 Total 43 44 Interest Principal Principal Remaining 45 46 47 48 49 50 51 52 153 Lod Equipment 3 Depreciation Factor Straight-Line (SLN) Yearly Cumulative Depreciated Year Deprecation Deprecation Asset Value Declining Balance (DB) Yearly Cumulative Depreciated Deprecation Deprecation Asset Value 1 Year Current Price Cost) Salvage Value Salvage Life of Asset Life 3 3 S 9 4 5 5 10 11 12 13 14 15 16 18 19 20 Loan Equipment