Question
For the excel lease assignment, I would like for you to complete two amortization tables. Follow these instructions (meant to make sure you can use
For the excel lease assignment, I would like for you to complete two amortization tables. Follow these instructions (meant to make sure you can use excel). Create each table on a separate worksheet (not file). Name each worksheet Ord ann Ann due. assume the following: a. 10 year lease b. payments of 5,000 happen quarterly c. Annual market rate of interest is 6% d. There is a bargain purchase option (BPO) of $30,000 The first 5 lines of the worksheet should have the following information with labels in column A and the numerical value (or formula to calculate the numerical value in column B. Number of compounding periods Principal amount (in other words the BPO) Interest Rate (adjusted to periodic amount) Quarterly payment Calculation of the pv of the annuity and the BPO. You can do this in two formulas or one, your choice. If you use two, add these together. The table shall contain 5 columns beginning after row 5. Col 1: Beginning Book Value Col 2: Interest Expense Col 3: Cash flow or payment Col 4: Reduction in Principal Col 5: Ending book value There should be nothing in the worksheet except formulas which will reference back to one of the calculations or inputs in rows 1-5. For example, your calculation of interest expense in the table will be a formula that references back to the cell address where you entered principal amount and the relevant interest rate. Using an absolute reference will make this easier for some of the work. Look that up in the help function if you dont know what that means. I will grade this by changing any value in rows 1-5. To get full credit, the entire worksheet should update accurately using the new values input in rows 1-5. Thus, the worksheet should work for any value in rows 1-5. For the worksheet Ord ann, you assume that the cash flow occurs at the end of each compounding period. For the worksheet Ann Due, you assume that the cash flow occurs at the beginning of each compounding period. The example from class was an annuity due. You can create a table for the annuity due, copy the work to the second worksheet and modify it some to be applicable for an ordinary annuity. Your last row, last column should equal to zero if you have done the table correctly.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started