There are the part of this question Preview File Edit View Go Tools Window Help
ID: 2396540 • Letter: T
Question
There are the part of this question
Preview File Edit View Go Tools Window Help O Search Search Highlight Rotate Markup Zoom Share View Long-Term Liabilities Using Excel for long-term notes payable amortization schedule atrick's Delivery Services is buying a van to help with deliveries. The cost of the vehicle is S35.000, the interest rate is 6%, and the loan is for three years. The van is to be repaid in three equal installment payments. Payments are due at the end of each year se the blue shaded areas on the ENTER-ANSWERS tab for inputs. rences and formulas where appropriate to receive full credit. Copy/pasting values will NOT earn full points. 1 Complete the data table. Enter all amounts as positive values. Do not use a minus sign or parentheses for any values 2 Using the present value of an ordinary annuity table, calculate the payment amo unt and complete the amortization schedule. arentheses for any values. Use the effective interest amortization method. Enter all amounts as positive values. Do not use a minus sign or p a. Calculate the loan payment by dividing the loan amount by the appropriate present value factor b. Round values to two decimal places Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year Use absolute cell references and relative cell references in formulas. C. Use absolute cell references C6 and C19 only to calculate interest expense and payment calculations 3 Using the Excel PMT function, calculate the payment amount and complete the amortization schedule Use the effective interest amortization method a. The PMT function calculates a payment amount that results in a negative number Reverse this to a positive number for calculations in the amortization schedule. Calculate the interest expense in the third year as the loan payment minus the loan balance at the beginning of the third year Use absolute cell references C6 and C39 only to calculate interest expense and payme b. Round values to two decimal places c Use absolute cell references and relative cell references in formulas nt calculations xcel Skils 1 Formulas using both absolute and relative cell references 2 PMT function xcel Hints PMT function uses the interest rate, the number of periods, and the loan amount (in that order). te the payment amount for a loan of $3,000 at 4% for 5 years, the formula would be PMT(4%, 5, 3000) 5673.88) o calcula untitled folder testing 3 Tari Al Screen Shot Question (e- 1 Question?.pdf 2018-...2.27 PM. escExplanation / Answer
Requirement 1:
Loan Amount = $35,000
Interest Rate = 6%
Number of periods = 3
Requirement 2:
Payment using PV table = Loan amount / Present value of ordinary annuity(6%, 3years) = 35000 / 2.6730 = $13,093.90
Requirement 3:
Payment using PMT Function = PMT(Interest Rate,Number of periods,Loan Amount) = PMT(6%,3,35000) = $13,093.84
Working Notes for the amortisation table:
- Interest Expense = Beginning Balance*6%
- Total Payment = PMT as calculated above
- Principal payment = Total Payment - Interest Expense
- Ending Balance = Beginning Balance - Principal Payment