Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Part 1: The formula to calculate the monthly payment, given loan L, term T and annual interest rate R is: LXR 12 R 1 1+_

image text in transcribedimage text in transcribed
Part 1: The formula to calculate the monthly payment, given loan L, term T and annual interest rate R is: LXR 12 R 1 1+_ 12XT ( 12) Excel also provides a PMT function to calculate the monthly payment for a loan. Its syntax is: PMT(rate, nper, pv) where Monthlypayment 2 Rate: The interest rate for the loan. For calculating the monthly payment, the annual interest rate is converted to monthly rate (dividing by 12). Nper: The total number of payments for the loan (Term *12). Pv: The principal. Prepare a monthly payment table for loans as shown below, rst using the formula and then use the PMT function. Your spreadsheet should allow user to change the term and the interest rate. It H Term 30 AnnualRate 6% Monthly Payment Loan Using formula Using PMT function Inblcn'ul'h 'w'M'II E g 8 Part 2: Use Excel to prepare an amortization table (see the example below) for a loan. There are three inputs to this program: Loan amount, Annual Interest Rate, and Terms. Use the payment function PMT to compute the monthly payment. The amount to interest column is calculated by multiplying the previous month's remaining balance by the monthly rate (annual rate! 12). Amount to principal is Payment Amount to Interest. Remaining Balance is previous month's remaining balance amount to principal. The table should be properly formatted. Test your worksheet with two sets of data: 1. Loan = 5000, rate = 12%, term = 1 year. 2. Loan = 10000, rate = 9%, term = 1.5 years (18 payments) Amortization Table Loan $5,000.00 Rate 12% Term{Year) 1 Payment Monthly Amount to Amount to Remaining Number Payment Interest Principal Balance 0 $5,000.00 1 $444.24 $50.00 $394.24 $4,505.75 2 $444.24 $45.05 $393.19 $4,207.57 3 $444.24 $42.03 $402.17 $3,305.40 4 $444.24 $33.05 $405.19 $3,399.21 5 $444.24 $33.99 $410.25 $2,933.95 5 5444.24 $29.39 $414.35 $2,574.51 7 $444.24 $25.75 $413.50 $2,155.11 3 $444.24 $21.55 $422.53 $1,733.42 9 $444.24 $17.33 $425.91 $1,305.51 10 $444.24 $13.07 $431.13 $375.34 P H 3.: h $3.75 $435.49 $439.35 $4.40. $439.35 $0.00 .4 M is: h

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Managerial Accounting For Managers

Authors: Eric Noreen

1st Edition

73526975, 978-0073526973

More Books

Students also viewed these Accounting questions

Question

Outline the steps in managing retention. LOP4

Answered: 1 week ago

Question

1. Background knowledge of the subject and

Answered: 1 week ago