If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 Calculate total interest earned on a loan and monthly payment
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## Calculate total interest earned on a loan and monthly payment

#1
December 22nd 04, 08:29 AM
 Matt Stanley external usenet poster Posts: n/a
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
December 22nd 04, 10:25 AM
 Gary T external usenet poster Posts: n/a

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
#3
December 22nd 04, 02:08 PM
 JE McGimpsey external usenet poster Posts: n/a

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
December 22nd 04, 05:26 PM
 Matt Stanley external usenet poster Posts: n/a

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

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

All times are GMT +1. The time now is 05:15 PM.