15 Use the Loan worksheet to complete the loan amortization table. In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25. 16 In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. 17 18 Now you want to determine how much interest was paid during the first two years. In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive. In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive. 19 20 You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87.
15 Use the Loan worksheet to complete the loan amortization table. In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.) In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25. 16 In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25. 17 18 Now you want to determine how much interest was paid during the first two years. In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive. In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive. 19 20 You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87.
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
Related questions
Question
100%

Transcribed Image Text:15. Use the Loan worksheet to complete the loan amortization table.
- In cell F2, insert the IPMT function to calculate the interest for the first payment. Copy the function to the range F3:F25. (The results will update after you complete the other functions and formulas.)
16. In cell G2, insert the PPMT function to calculate the principal paid for the first payment. Copy the function to the range G3:G25.
17. In cell H2, insert a formula to calculate the ending principal balance. Copy the formula to the range H3:H25.
18. Now you want to determine how much interest was paid during the first two years.
- In cell B10, insert the CUMIPMT function to calculate the cumulative interest after the first two years. Make sure the result is positive.
19. In cell B11, insert the CUMPRINC function to calculate the cumulative principal paid at the end of the first two years. Make sure the result is positive.
20. You want to perform a what-if analysis to determine the rate if the monthly payment is $1,150 instead of $1,207.87.

Transcribed Image Text:### Loan Information Spreadsheet
This spreadsheet provides details about a loan and explores different payment strategies.
#### Loan Details
- **Principal Value (Loan PV):** $75,000.00
- **Annual Percentage Rate (APR):** 5.00%
- **Monthly Rate:** 0.42%
- **Loan Term:** 5 Years
- **Number of Payments (NPER):** 72 months
- **Monthly Payment:** $1,207.87
#### Payment Breakdown
The table tracks each payment with columns for:
- **Payment #:** Sequential number of payments.
- **Beginning Balance:** Starting balance before payment.
- **Interest:** Interest amount for each period.
- **Principal:** Portion of payment applied to principal.
- **End Balance:** Resulting balance after payment.
The cells for these details are currently unfilled.
#### Cumulative Totals After Year 2
- **Interest:** Empty cell intended for total interest paid by the end of Year 2.
- **Principal Paid:** Empty cell to accumulate principal payments by the end of Year 2.
#### Alternative Payment Goal
- **Target Monthly Payment:** $1,150.00
To achieve this target:
- **Monthly Rate Needed:** Determines what adjusted monthly interest rate is required.
- **APR Needed:** Calculates the necessary annual percentage rate.
All related values for this scenario are unfilled. This exploration helps assess different financial strategies.
Expert Solution

This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images

Recommended textbooks for you

Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education

Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON

Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON

Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education

Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON

Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON

C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON

Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning

Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education