Data File Needed: CorpTech.xlsx

Project: 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.

Modify the Workbook

1. Open the CorpTech.xlsx data file and save it as XX_CorpCosts.xlsx, substituting the XX with your initials.
2. In the Loan worksheet, enter the loan schedule information shown on the right in the range of B4:B6, and B8.
3. In cell B7, calculate the Period Rate (Rate).
4. In cell B9, calculate the Payments (NPER).
5. In cell B10, calculate the quarterly Payment (PMT). Show as a negative amount as cash is paid out.
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.
8. In cell E16, using the PPMT function, calculate the Principal Payment.
9. In cell F16, calculate the Total Payment by adding the Interest Payment to the Principal 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 end of the period.
15. In cell B44, 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 Loan (PV) amount to the Year 1 Principal. 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 C45 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:

1. In cell E5, using the SLN function, calculate the Straight-Line Depreciation. Copy the formula in cell E5 and paste it to E6:E14.
2. In cell F5, using a formula, refer to the Year 1, Yearly Depreciation in cell E5 to display the depreciation for the first year.
3. Calculate the Cumulative Depreciation in cells F6:F14. (Hint: in cell F6, add Year 1 Depreciation to the Year 1 Cumulative Depreciation. Auto fill the formula F6 through F14.
4. In G5, calculate the Depreciated Asset Value by subtracting the Cumulative Depreciation from the Current Price. Copy the formula to cells G6:G14. Compare your first five years with the screen print to the right. (Hint: In Year 10, Depreciated Asset Value under Straight line will be $20,000)

1. In cell J5, using the DB function, calculate the Declining Balance Depreciation. Auto fill the formula through J14.
2. In cell K5, using a formula, refer to the Year 1 Yearly Depreciation in cell J5 to display the Depreciation for the first year.
3. Calculate the Cumulative Depreciation in cells K6:K14. (Hint: in cell K6, add Year 1 Cumulative Depreciation to Year 2 Yearly Depreciation. Auto fill the formula through K14.)
4. In L5, calculate the Depreciated Asset Value by subtracting the Cumulative Depreciation from the Current Price. Copy the formula to cells L6:L14. Compare your first five years with the screen print to the right. (Hint: In Year 10, Depreciated Asset Value under Declining Balance will be $20,108)
5. Save and close the workbook.