Home 
Search 
Today's Posts 
#1




Calculate total interest earned on a loan and monthly payment
I plan to finance part of a service contract for a client... We're getting
paid for half of the contract up front and financing the rest... Here's what I need help with: Using Excel, I need to calculate the total interest earned over the life of the loan based on the amount of principal, the number of months, and the APR  each of which is in a separate cell. I also need to compute the monthly payment for our client. FYI  on our client's monthly payment, I used the PMT Financial Function but it gave me a negative number so either it's not working right or I'm doing something wrong. What are the necessary formulas to accomplish these two objectives??? Please help... TIA!  Sincerely, Matt Stanley 
#2




OK
Let's assume the principal is in cell A1 (make this the amount of the Contract divided by two), the number of months in A2 and the APR in A3. To begin with, Excel displays the result of the PMT function as a negative, as this is what is most commonly required (i.e. the PMT function is used extensively for Cash Flow forecasts, etc, and so showing the Loan Repayment as a negative makes sense  i.e. for companies borrowing money, their repayment will be an outflow of money and hence a negative). Now, I assume you already have the right parameters in PMT, but for completeness, in A4 put: =PMT(A3,A2,A1) where the parameters of PMT a =PMT(rate,number of payments,present value). Note the negative prior to the PMT argument  this reverses the negative that you are seeing. Now, this will give you the amount repayable each month to your contractor. Multiplying this amount by the number of months will give you the total amount payable to the Contractor for the "Financed" part of the Contract. So, in A5, type: =A4*A2. Now all you have to do is subtract (from this total amount) the original principle to be paid (i.e. A1), to arrive at the interest portion of the contract. So in A6, enter: =A5A1. Happy to Help, Gary Thomson 
#3




PMT's results reflect the "direction" of money. You can think of it in
terms of negative results requiring expenditure and positive amounts being income. So if you're taking out a load for $10,000, you receive the money, so the value is positive. The payment is negative to represent expense. Of course, you can also think of it as the reverse... In your case, I'd make the PV values negative to reflect your extending credit, then the payment result will be positive to reflect your income stream. In article , "Matt Stanley" wrote: FYI  on our client's monthly payment, I used the PMT Financial Function but it gave me a negative number so either it's not working right or I'm doing something wrong. 
#4




THANKS Gary T! This has been kicking my butt for a WEEK but now I understand
what you're saying about how the PMT function works. I now have the answers I need to get this spreadsheet finished, and that's a great Christmas gift. You rock! Happy Holidays to you and yours. Sincerely, a very grateful Matt S "Gary T" wrote in message ... OK Let's assume the principal is in cell A1 (make this the amount of the Contract divided by two), the number of months in A2 and the APR in A3. To begin with, Excel displays the result of the PMT function as a negative, as this is what is most commonly required (i.e. the PMT function is used extensively for Cash Flow forecasts, etc, and so showing the Loan Repayment as a negative makes sense  i.e. for companies borrowing money, their repayment will be an outflow of money and hence a negative). Now, I assume you already have the right parameters in PMT, but for completeness, in A4 put: =PMT(A3,A2,A1) where the parameters of PMT a =PMT(rate,number of payments,present value). Note the negative prior to the PMT argument  this reverses the negative that you are seeing. Now, this will give you the amount repayable each month to your contractor. Multiplying this amount by the number of months will give you the total amount payable to the Contractor for the "Financed" part of the Contract. So, in A5, type: =A4*A2. Now all you have to do is subtract (from this total amount) the original principle to be paid (i.e. A1), to arrive at the interest portion of the contract. So in A6, enter: =A5A1. Happy to Help, Gary Thomson 
Reply 
Thread Tools  Search this Thread 
Display Modes  

