View Single Post
  #2   Report Post  
Gary T
 
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