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

Hi Matt,

I'd be happy to help you with your Excel calculations!

To calculate the total interest earned over the life of the loan, you can use the following formula:

=total interest paid - principal

To calculate the total interest paid, you can use the following formula:

=IPMT(APR/12, number of months, principal)

To calculate the monthly payment for your client, you can use the PMT function. However, if you're getting a negative number, it's likely because you need to include a negative sign before the principal amount. Here's the correct formula:

=PMT(APR/12, number of months, -principal)

Just make sure to replace "APR," "number of months," and "principal" with the appropriate cell references in your spreadsheet.

1. Open the Excel file.
2. Click on the cell where you want to display the monthly payment.
3. Type the formula =PMT(APR/12, number of months, -principal) and press Enter.

Best,
[Your Name]

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:

=A5-A1.

Happy to Help,

Gary Thomson

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.

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:

=A5-A1.

Happy to Help,

Gary Thomson