Question
Download the file called Amortization Table 1 from this question: Amortization Table 1.xlsx The file contains loan information and an amortization table for a $100,000
Download the file called Amortization Table 1" from this question: Amortization Table 1.xlsx
The file contains loan information and an amortization table for a $100,000 mortgage. The amortization table contains formulas that allow you to enter loan information and produce a payment schedule for a mortgage. Familiarize yourself with all the components of the worksheet. Note: To solve for a blended mortgage payment, first solve for the effective rate per payment period. In this case this involves converting from the quoted rate compounding semi-annually to the effective rate per year (i.e., compounded annually). Mathematically, effective rate per period (annual) = i = (1+QR/2)^(2/1), then solve using the PV annuity equation: PMT = PV/[((1-(1+i)^-n))/(i))] In this case, using the Excel Payment function =ROUNDUP(PMT($C$11,$C$4*$C$8,-$C$3,0,0),2) rounded up to the next higher cent. More generally, the Excel PMT function is = PMT(rate,nper,pv,fv,type).
Create a new amortization table from Table 1 for a mortgage with $85,000 loan amount, monthly payments; a quoted rate of 5.0% compounded quarterly, 25 year amortization, and a 5 year term. Your table should contain the amortization schedule for 60 payments and show the outstanding balance at the end of the 5 year term. Use the existing worksheet as a template and change the loan information to the above values. Use the "fill-down" command to copy the existing formula in order to enlarge the amortization table to accommodate 60 scheduled payments. Please include cumulative/principal paid in table.
Q1: What is the principal balance owing at the end of the term?
Q2: What is the principal balance owing at the end of the term?
Q3: What is the cumulative amount of interest and principal paid over the term?
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