Question

1. Enter a formula in cell C9 using the PMT function to calculate the monthly payment on a loan using the assumptions listed in the Status Quo scenario. In the PMT formula, use C6 as the monthly interest rate (rate), C8 as the total number of payments (nper), and C4 as the loan amount (pv). Enter this formula in cell C9, and then copy the formula to the range D9:F9.

C9 A B D E F G 1 2 SpringLeaf Designs - Loan and Revenue Scenarios Conservative Status Quo 3 Growth 4 5 Loan Term (Yrs) Total Loan Amount Annual Interest Rate Monthly Interest Rate Loan Term (Yrs) Total Number of Payments Monthly Payment Amount $0 7% 0.40% $150,000 7% 0.40% Moderate Aggressive Growth Expansion $350,000 $ 450,000 7% 7% 0.40% 0.40% 4 5 48 60 6 7 3 3 36 8 36 9 10 Moderate Current Year Conservative Growth 11 Revenues Growth 12 $ 13 Head Accessories Arm Accessories Body Accessories Total $ 3,047,520 $ 3,591,816 $ 5,289,720 $ Aggressive Expansion 5,333,160 6,285,678 9,257,010 3,352,272 $ 3,950,998 $ 5,818,692 $ 4,266,528 $ 5,028,542 $ 7,405,608 $ 14 $ 15 16 17

Answer 1

Prerequisite Knowledge:

Basic knowledge about Functions in Excel (How it works, how do we write a function, etc)

Solution:

Since the question has already informed us to use the PMT function to calculate the monthly payments, we will be using the PMT function usin C6 as the monthly interest rate (rate), C8 as the total number of payments (nper), and C4 as the loan amount (pv) as mentioned in the question.

• C9: =PMT(C6,C8,C4)

• D9: Here we get to see a twist. If we simply copy paste the formula from C9, we get to see a negative amount.

The reason is, Payments are always considered something we give to the lender/bank. So, Excel returns it as a negative number. To make it a positive amount, we pass the PV value as a negative one as follows:

=PMT(D6,D8,-D4)

• E9, F9:

We can simply copy paste the formula from D9 and it would work fine.

However, here are the formulae for each.

E9:   =PMT(E6,E8,-E4)

F9: =PMT(F6,F8,-F4)

NOTE: There are no Instructions given in here in this uploaded question for the values to be written in C15 to F15. If such is the case, we can assume it to just be the sum of the data present above. Example, C15=C12:C14. After which, you may copy the same formula for D15 to F15.

----------------------------------------------------------------------------------------------------------------------------------------------------

Please leave an upvote if you are satisfied with the answer and if you still have any queries, feel free to drop them in the comments section below. We will get back to you and help you out with them!

Thanks, Keep Learning, Keep Chegging!! :)