Calculating a loan

[Guerilla]

Hi,

How do I write a formula which enable me to compare different loans?
I will need to be able to type in the term, amount, interest rate
ect. I would like Excel to calculate the repayments based on the
input of the above criteria in other cells. I would also then like
the total repaid and the total interest displayed.

hope someone can help,

cheers

Matt

 

[Bernard Liengme]

Use Help to lean about the PMT function then return for more help
best wishes

 

[Jim Cone]

To just get the results, the free Excel add-in "Calculate Payments" can
be downloaded here... http://www.realezsites.com/bus/primitivesoftware
No registration required.
--
Jim Cone
San Francisco, USA


"Guerilla" <[email protected]>
wrote in message
Hi,
How do I write a formula which enable me to compare different loans?
I will need to be able to type in the term, amount, interest rate
ect. I would like Excel to calculate the repayments based on the
input of the above criteria in other cells. I would also then like
the total repaid and the total interest displayed.
hope someone can help,
cheers
Matt

 

[joeu2004]

How do I write a formula which enable me to compare different loans?
I will need to be able to type in the term, amount, interest rate
ect. I would like Excel to calculate the repayments based on the
input of the above criteria in other cells. I would also then like
the total repaid and the total interest displayed.

To some extent, the answer depends on the jurisdiction. For example,
Canadian loans calculations are somewhat different. For a US loan....

If A1 is the term in years, A2 is the annual rate, and A3 is the loan
amount, the monthly payment can be computed by (in A4, say):

=pmt(A2/12, A1*12, -A3)

That value should be rounded at least to cents (for US). But the
degree of rounding or truncation is arbitrary, depending on the
lender.

Once you determine the __rounded__ payment amount, usually the final
payment is different from the others. You should recompute the number
of periods (in A5) and the final payment (assuming a full-term loan)
as follows:

=roundup(nper(A2/12, A4, -A3), 0)

=fv(A2/12, A5-1, A4, -A3)*(1 + A2/12)

Again, the FV() should be round at least to cents (for US). It is
prudent to compute NPER() because if you round up the payment "too
much", you might reduce the term of the loan. But normally that does
not happen.

 

[Guest]

PS.... I see that I failed to answer all of your questions. And I have been
having trouble posting a follow-up response.

For a US loan....
If A1 is the term in years, A2 is the annual rate, and A3 is the loan
amount, the monthly payment can be computed by (in A4, say):
=pmt(A2/12, A1*12, -A3)
[....]
Once you determine the __rounded__ payment amount, usually the final
payment is different from the others. You should recompute the number
of periods (in A5) and the final payment (assuming a full-term loan)
as follows:
=roundup(nper(A2/12, A4, -A3), 0)
=fv(A2/12, A5-1, A4, -A3)*(1 + A2/12)

Suppose the final payment is in A6. Then the total repaid is (in A7):

=A4*(A5-1) + A6

The total interest is:

=A7 - A3

By the way, in comparing some loans (especially mortgages), you might also
need to take other things into account, such as points and prepaid interest
(for the initial odd period).

Ostensibly the APR is intended to be used to compare loans and avoid all of
this computation. But as you may know already, it is really a poor
comparator if you rely on the institution to specify the APR because there is
some latitude in what each institution chooses to include in their APR
computation :-(.

That said, if you are still interested in how to compute the APR, that is a
different question altogether; and to answer that again, we would need to
know the kind of loan (mortgage or not). Post again if you are interested.
But frankly, I think you were wise not to ask about it in the first place .