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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating a loan
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 |
#2
|
|||
|
|||
Calculating a loan
Use Help to lean about the PMT function then return for more help
best wishes -- Bernard V Liengme www.stfx.ca/people/bliengme remove caps from email "Guerilla" wrote in message ups.com... 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 |
#3
|
|||
|
|||
Calculating a loan
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" 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 |
#4
|
|||
|
|||
Calculating a loan
On Jan 31, 10:15 am, "Guerilla" wrote:
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. |
#5
|
|||
|
|||
Calculating a loan
PS.... I see that I failed to answer all of your questions. And I have been
having trouble posting a follow-up response. On Jan 31, 10:43 am, I wrote: 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) On Jan 31, 10:15 am, "Guerilla" wrote: I would also then like the total repaid and the total interest displayed. 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 :-). |
Thread Tools | |
Display Modes | |
|
|