Question

Long-term notes payable amortization schedule 

Dana's Delivery Services is buying a van to help with deliveries. The cost of the vehicle is $42,000, the interest rate is 6%, and the loan is for three years. 6 The van is to be repaid in three equal installment payments. Payments are due at the end of each year. 

 Requirements

 1. Complete the data table.

 2. Using the present value of an ordinary annuity table, calculate the payment amount and complete the amortization schedule. Use the effective interest amortization method.

 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 year 3 as the loan payment minus the loan balance at the beginning of the third year.

 c. Use absolute cell references and relative cell references in formulas.

 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,

 b. Round values to two decimal places. Calculate the interest expense in year 3 as the loan payment minus the loan balance at the beginning of year 3.

 c. Use absolute cell references and relative cell references in formulas. 


Excel skills

 1. Formulas using both absolute and relative cell references.

 2. PMT function 


Excel Hints 

The PMT function uses the interest rate, the number of periods, and the loan amount (in that order). 

To calculate the payment amount for a loan of $3,000 at 4% for 5 years, the formula would be EPMT(4%, 5, 3000) 

Requirement 1 Complete the data table. 2 DATA Loan Amount Interest Rate Periods 11 12 13 14 15 Requirement 2 Using the presen

3 0
Add a comment Improve this question Transcribed image text
Answer #1

Requirement 1 :

DATA
Loan Amount $ 42,000
Interest Rate 6 %
Periods 3

Requirement 2 :

a. Loan payments = $ 42,000 / 2.673 = $ 15,712.68

Period Beginning Balance Principal Payment Interest Expense Total Payment Ending Balance
0 $ 0 $ 42,000
1 42,000 $13,192.68 2,520 $ 15, 712.68 28,807.32
2 28,807.32 13,984.24 1,728.44 15,712.68 14,823.08
3 14,823.08 14,823.08 889.60 15,712.68 0
Total $ 42,000 $ 5,138.04 $ 47,138.04

b. Interest expense in the third year = $ 889.60

Add a comment
Know the answer?
Add Answer to:
Dana's Delivery Services is buying a van to help with deliveries.
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • Requiremeht1 Complete the data table DATA Loan Amount Interest Rate Periods 35,000 6% Requirement 2 Using...

    Requiremeht1 Complete the data table DATA Loan Amount Interest Rate Periods 35,000 6% Requirement 2 Using the present value of an ordinary annuity table, calculate the payment amount and complete the amortization schedule Use the effective interest amortization method. 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...

  • PLE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ADD-INS QuickBooks 614 X f r --PMT(4010/12,011*09,08)...

    PLE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ADD-INS QuickBooks 614 X f r --PMT(4010/12,011*09,08) HT Name: kelly gamer Your score On January 1, 2020 the Kelly Gamer Company borrows $150,000 cash by signing a 5-year, 10%, installment note, with semiannual interest payments. 1) Calculate the amount of each payment using the PMT function. 2) Prepare the amortization schedule for the loan. Enter a valid Excel formula or function in each of the yellow cells below. Formulas must refer...

  • 1. In Cell D8 create formula PMT=PV/((1-1/(1+k)^n)/k), to calculate the periodic payment on a loan. 2....

    1. In Cell D8 create formula PMT=PV/((1-1/(1+k)^n)/k), to calculate the periodic payment on a loan. 2. In cell F8 use built-in function =PMT(k,n,PV). You should get identical answers. 3. Create amortization table (use absolute and relative addressing where appropriate). 4. Print worksheet. 5. Change loan amount and the rate (everything should adjust automatically) and print it again. 6. Print the cell formulas (force to one page). 7. Write report and explain all formulas and procedures. 8. Submit four printouts. PMT=$1589.99...

  • You will compare 2 different amortization schedules when buying a car. The purchase price is $17,500....

    You will compare 2 different amortization schedules when buying a car. The purchase price is $17,500. Bank A requires a 20% down payment and has an annual interest rate of 3.6%. Bank B wants only 10% down payment but the annual interest rate is 4.8%. In both banks, the loan will be paid off in 3 years. Formulas: A.First you need to set up the formula to find the monthly payment. Use the PMT function under FINANCIAL FORMULAS. B. After...

  • Fulton Corporation purchases new manufacturing facilities and assumes a 10 year mortgage of $2 million. The...

    Fulton Corporation purchases new manufacturing facilities and assumes a 10 year mortgage of $2 million. The annual interest rate on the mortgage is 5.5% and payments are due at the end of each year. a. Determine the mortgage payment that Fulton Corporation must make each year. Round to the nearest dollar. b. Use Excel to prepare a mortgage amortization schedule for the 10 years. c. At the end of the first year, what amount will Fulton include as "current maturities...

  • Excel is allowed! For this lab, we will create a spreadsheet that allows somebody to type...

    Excel is allowed! For this lab, we will create a spreadsheet that allows somebody to type in a loan amount, interest rate, and length of the loan in years. The spreadsheet will then calculate the monthly payment required and the actual amount paid on the loan. First, setup your spreadsheet: • In Cell A1, put the label Loan Amount:. The corresponding value would be input in Cell B1. • In Cell A2, put the label Interest Rate:. The corresponding value...

  • MUST SHOW ALL CORRECT EXCEL FORMULAS 5 X FILE HOME INSERT X Arial Amortization with equal...

    MUST SHOW ALL CORRECT EXCEL FORMULAS 5 X FILE HOME INSERT X Arial Amortization with equal payments - Excel PAGE LAYOUT FORMULAS DATA REVIEW VIEW Sign In 12 - A A % Alignment Number Conditional Format as Cell Cells Editing Formatting Table Styles Styles fx Prepare an amortization schedule for a five-year loan of $67,500. The interest Paste B 1 U Clipboard Font C3 HE с D E G H 1 2 2 3 Prepare an amortization schedule for a...

  • "You are thinking of purchasing a house. The house costs $350,000. You have $50,000 in cash...

    "You are thinking of purchasing a house. The house costs $350,000. You have $50,000 in cash that you can use as a down payment on the house, but you need to borrow the rest of the purchase price. The bank is offering a 30-year mortgage that requires annual payments and has an interest rate of 7% per year. You can afford to pay only $23,500 per year. The bank agrees to allow you to pay this amount each year, yet...

  • You just graduated college with your Bachelors Degree and accepted a job offer at your dream...

    You just graduated college with your Bachelors Degree and accepted a job offer at your dream company. On January 1, 2020, you decide to celebrate by purchasing a Mercedes Convertible (see picture above) for $52,455. You have a 720 credit score, therefore you were able to get a 5% interest rate on a 7 year loan. You will be making monthly payments. Using Excel, prepare a professional amortization schedule for the entire 7 years. It must include the payment and...

  • should be explain it on excel Solve all of the following problems with Excel. Please use formulas in excel to solve. (2) (10 pts) (a) Assume monthly car payments of $500 per month for 4 years and...

    should be explain it on excel Solve all of the following problems with Excel. Please use formulas in excel to solve. (2) (10 pts) (a) Assume monthly car payments of $500 per month for 4 years and an interest rate of 0.75% per month. 1. What initial principal will this repay? (b) Assume annual car payments of $6000 for 4 years and an interest rate of 9% per year. 1. What initial principal will this repay? (c) Assume monthly car...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT