Tech Support Guy banner

Help with excel PMT and Average

3090 Views 11 Replies 3 Participants Last post by  The Villan
Hi all I am using Excel and i am trying to work out how to do the following:

I am doing a Excell excersize for college (to be handed in when we return) and I have to use the 'PMT' function to calculate the monthly repayments of a loan in the spreadsheet - How is this done. MS Online does'nt seem to be very helpful :S

with my calculations () It has came up with -£132 per month as payment which is obviously not right can anyone advise on the formula I shound be entering the spreadsheet states:

Loan amount £10,000
No. of Years 10
Annual interest rate 10%
Monthly repament = ?

The other problem with another spreadsheet i am having is I don't know how to get the average of something AND round it to the nearest whole number can anyone advise on how this would be done ?

Not open for further replies.
1 - 6 of 12 Posts
In principle we are not supposed to give the answer fior people studying (at least thats what I understand). However, we can help you to try and understand your result.

Firstly, why don't you think the figure for PMT is correct - is it becuase the result is a minus figure?

The result is negative because it represents money that you would pay

Secondly is the 10,000 meant to be present value or future value?



For a more complete description of the arguments in PMT, see the PV function.

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

The payment returned by PMT includes principal and interest but no taxes, reserve payments, or fees sometimes associated with loans.
Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12 percent for rate and 4 for nper.

The above information is from the fx help in Excel itself. I always used to teach people to use the FX help facility if they wanted to understand the syntax of the formula. I am not sure if you know how to do that, so I will jump in with both feet and explain how that is done :)

Click on a blank cell in the spreadsheet.
In the formula bar you will see an object that says "fx" - click on it once
When the Insert Function Dialogue Box appears - immediately type PMT and hit the Enter key on your keyboard
PMT should be the only function showing now.
In the bottom left hand corner of the dialogue box you will see text that is in blue which says "Help on this function" - Click on this once and all the help should appear on this particular function. There are examples of the sort of thing you are trying to do in there.

Does the above help in any way? If not come back and ask for any help on understanding the syntax and or the result
See less See more
For the =Average function, you will need to enclose the average function with the =round function. Called nesting of functions.

Again, if you need to understand the round function go through the same principles as I have mentioned in my previous post, only use round instead of pmt

Here is a link that gives info about nesting functions

Again come back if there is something you can't get your head around and need further explanation.
Agree with you there Firetytr. Howveer if DrBob has been instructed to round the average, thats what has to be done. If he has to format the result to 2 decimal places then thats a different issue.
Not sure if that has changed, but Excel has always worked to 15 numbers of accuracy.
I doubt that the result that DrBob has to produce requires too much accuracy especially as we are talking about average.
Maybe DrBob can enlighten us on that.
My assumption was based on DrBob using PMT. I naturally assumed that s/he was a bit more advanced and theerfore did mean round rather than format.

Does that make sense :)
Sort of. Had this nasty cough/cold that seems to be going the rounds. Sore ribs and all that.
So it was spent indoors with family and to bed by one. :)

Was yours a good en ?
as for help I found out about this and It is ok for me to receive advice and explanations as long as it isnt blatently just being done for me.

Well DrBob
I think we kept within thiose guidelines :)

Anyway what were you doing wrong if anything and what were your answers. It always helps to get feedback.
1 - 6 of 12 Posts
Not open for further replies.