A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
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



 
 
Thread Tools Display Modes
  #1  
Old December 22nd 04, 08:29 AM
Matt Stanley
external usenet poster
 
Posts: n/a
Default 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



Ads
  #2  
Old December 22nd 04, 10:25 AM
Gary T
external usenet poster
 
Posts: n/a
Default

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

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

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

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


All times are GMT +1. The time now is 10:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.