loan calculator

[System_Error]

Someone tell me how you calculate loan amount if you are given the amount(principle), the interest rate, and the number of months. I've sat here for an hour and can't figure it out. I keep getting different results from these other websites that have loan calculators.

[wossname]

For a load of 3.7% (per month) interest...

Amount owed = Principle * (1.037 ^ Months)




I'm not a financial advisor though so don't expect this to be right.

[wey97]

amount = amount to finance
rate = rate per period expressed as a decimal (12% = 0.12) / 12 to get the rate per month = 0.01
term = number of periods you want to finance (i.e. 5 years = 60 months)

The formula to calculate the monthly payment is:
pmt = (amount * rate) / (1 - (1 + rate) ^ -term)

where x^y means x raised to the y power.

So if you want to finance $10,000 at the rate of 0.01 for 60 months the payment would be:
pmt = (10,000 * 0.01) / (1 - (1 + 0.01) ^ -60)
pmt = $222.44

The total interest paid for the loan would be pmt * term - amount
or the average interest per month would be pmt - amount / term

but remember the interest on an amortized loan varies over the life of the loan so you don't pay a fixed amount of interest every month. You pay more interest upfront and the interest paid per month decreases exponentially with the life of the loan.

[System_Error]

Thank you SOOO much..

[Rassis]

Someone tell me how you calculate loan amount if you are given the amount(principle), the interest rate, and the number of months. I've sat here for an hour and can't figure it out. I keep getting different results from these other websites that have loan calculators.

If I understood the problem right, here you have the answer you are looking for - see an example in the attached Excel file.

Good luck.

Rui

[System_Error]

Thanks. I really like how you did that.

[wellsm]

Hi Rassis,

I like the formula although the figure varies slightly (probably due to rounding) from the excel =PMT function.

My question is this:

I am trying to code the equivalent to the EXCEL PMT function.

in VB, the formula looks like this:

PMT = ((PV - FV) * rate/ (1 - (1 + rate) ^ -(nper)))

This is great for PMT calcs where Type =0 (i.e. payments occur at the end of the period)

However, I'm trying to code for a Type 1 scenario (i.e. payments occur at the start of the period).

For example, using =PMT(6.25/12,36,15000,0,0) gives 458.03 (the same as my formula) whereas =PMT(6.25/12,36,15000,0,1) gives 455.66

Can you tell me how to modify my formula?

All the best,

M

[wellsm]

Okay. Please ignore my post.

Having researched a bit more on this site, I just realised that I can use the Excel PMT function in my code!

I wasted a lot of time getting there but it was worth it.

[Rassis]

I am sorry for answering so late but I have been fully dedicated to my PhD thesis over the past few weeks.

I join herewith another example - slightly different from the previous one that I posted and more general. This time it includes your data and a deferred period. The PMT Excel function is also present.

I hope this helps a little bit more.

Have a nice time.

[wellsm]

Hi Rassis,

Thanks you so much for taking the trouble to post the spreadsheet in answer to my post.

I have to admit that Maths is not a strong point with me and even though I sort of understand the formula, I can never get to a level of understanding where I am able to modify it to my own needs. Though believe me when I say that I've tried.

IF you have the time (and the inclination) could I ask you to consider this problem:

The repayment figures I am trying to come up with are based on the following:

A loan of £16000 at 8.5% per annum based on 48 repayments BUT with 3 of those payments paid on Day 1 and the remaining payments paid at the end of each of the following 45 months.

The figure I am trying to achive is £389.78

Another example is £12000 at 6.5% over 60 months BUT where 1 payment is paid on day one and the remaining payments paid at the end of each of the following 59 months.

The figure I am trying to achive is £233.53.

If you are able to help I would be extremely grateful.

Best wishes,

wellsm

[Rassis]

Martin,

The examples you presented are quite unusual, nevertheless I have an answer for them although not exactly a straight one. My understanding of the problem led me to three alternatives of solution which I attach herewith in a Word file.

I hope this helps.

Regards.

[Rassis]

Because so many forum posters showed interest in this topic, I decided to add an EXCEL file as a complement which allows one to solve almost any problem involving inflows and outflows of money over time (a period can be a year, a semester, a quarter, a month, a week, a day, an hour,...)

I hope you all enjoy.

[System_Error]

Hmm, do you by any chance know how to calculate prepayments, tax, or inflation if the user decides to? I'm wanting to add these options to my program, but I can't seem to find any information online as to how to do this.

[Rassis]

Inflation

Suppose you are appraising a project which will extend for 6 years and you want to adjust your minimum attractive rate of return (MARR) by the mean inflation rate expected over the next six years.

If you estimate inflation rates to be 0,055; 0,054; 0,056; 0,049; 0,045; 0,041 for the next six years, then, you calculate the mean inflation rate inf, by doing:

inf = [(1 + 0,055) x (1 + 0,054) x … x (1 + 0,041)]^(1/6) – 1 = 0,050

If MARR is 10%, then, you adjust MARR the following way:

MARR(adj) = (MARR – inf) / (1 + inf) = (0,10 – 0,05) / (1 + 0,05) = 0,0476 or 4,76%

This means that 10 – 4,76 = 5,24% will end up by being “eaten” by inflation.

Prepayments – will you please be more specific?

Tax – will you please be more specific?

[System_Error]

Thanks for the help. That cleared a lot of things up. Now, I think I figured out the prepayment and tax so you don't have to worry about that. One more thing though..I would like to add the option of inflation as a special effect(sort of) in my program. I was wondering if it would be better to have my program allow the user to enter one inflation rate over the term of their loan(which doesn't make much sense to me), or if they opt to show the impact of inflation, have my program estimate what the inflation would be over the term of the loan. If that doesn't make sense, let me know and I'll try to explain a bit better. I just saw a mort calculator that allowed the user to enter the rate of inflation for the ENTIRE loan term(as one rate), but that doesn't make much sense because it would be different each year? So I'm thinking it would be better to go by some alogorithm and do it programtically instead of letting the user decide...Do you?

[Rassis]

I once developed a program myself where estimated inflation rates had to be considered to account for the loss of value of money over the time span of an investment project. I did it the way you can see in the attached figure and it did work out fine. Legends are in portuguese but it looks very much like the case in the example that I posted the other day. There is just a small adjustment at the end, to transform the annual tax rate into a semesterial tax rate, by doing: (1 + i)^(1/2) – 1 where i is the annual tax rate.

I hope this helps, otherwise please insist.