Question

8. What is the payment in month 13 on a CAM loan for $150,000 with a maturity of 15 years at 5.85%? Answer Loan Amount Years Periods Per Year Payment Month at Issue Here Interest Rate Constant Amortization Amount 9. What is the interest paid in month 14 on the loan in the previous question? Answer Loan Amount Years Periods Per Year Payment Month at Issue Here Interest Rate Constant Amortization Amount

please use excel functions

Answer 1

8]

Monthly loan payment is calculated using PMT function in Excel :

rate = 5.85% / 12   (converting annual rate into monthly rate)

nper = 15*12 (15 year loan with 12 monthly payments each year)

pv = 150000 (loan amount)

PMT is calculated to be $1,253.66

=PMT(5.85%/12,15*12,150000) B C D E F A ($1,253.66)! 11

The payment in every month during the 15 year loan term is $1,253.66 as it is a constant amortizing loan in which the payments are level every month.

Answer 2

Disclaimer: I know the amount is correct because I checked it against an amortization schedule I made on another spreadsheet. However, I am unsure if this is what the system deems the "correct" way to do the equation since it seems a little over-complicated. Thus, while the number may be correct, the system may make it as "wrong" if it uses a different equation.

This may not be the way the program wants you to find it, however, you can find the answer this way:

1. Fill in the information you have:
Loan Amount: $150,000

Years: 15

Periods Per Year: 12

Payment Month at Issue Here: 13 

Interest Rate: 5.85%

2. Find the Constant Amortization Amount. This is the loan amount divided by the monthly rate. For simplicity's sake, the Constant Amortization Amount is $833.33. Double check that this is the amount you get.

3. The balance at the end of the previous period/beginning of the current period is equal to the total loan amount minus the amount paid into principal up to that point. The present value at the end of period 12 is the loan amount ($150,000) minus the Constant Amortization (833.33) times the number of months up until then (12). Thus, the equation for the present value would be 150000-(833.33*12).

4. To find the interest payment, multiply this present value by the monthly interest rate. 

5. From there, the total payment is equal to the Constant Amortization Amount plus the interest payment. 

Thus, =B82+((B77-(B82*B80-1))*(B81/B79))

Breakdown of Equation: 

Total Payment = Constant Amortization Amount + Interest payment in period 13

Interest payment in Period 13 = Present Value at end of period 12 * the Monthly Interest Rate

Present Value at end of period 12 = Loan Amount - (Constant Amortization Amount * ("Payment Month at Issue Here" - One)