[DrBob]

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 ?

Thanks

 

[The Villan]

DrBob
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?

>>Syntax

PMT(rate,nper,pv,fv,type)

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

Remarks
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

 

[The Villan]

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 http://www.bettersolutions.com/excel/EDH113/LI749589511.htm

Again come back if there is something you can't get your head around and need further explanation.

 

[Zack Barresse]

..
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 ?

You find yourself in a very precocious situation here. Actually rounding the numbers will truncate the number as Excel deems fit (through the necessary algorithm of the round function you give it, which all behave slightly different). There is a difference between a format and a value (e.g. ROUND, ROUNDUP, ROUNDDOWN, TRUNC, INT, CEILING, FLOOR, etc). A format is how you want to show your value. You can have a value show two decimal places, but it may in fact be more than that. What happens if you round that? You take away a portion (or add to it) which can be compounded and give you differences of pennies, but it all adds up.

HTH

 

[The Villan]

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.

 

[Zack Barresse]

Yes, DrBob will need to decide, just wanted to make sure that s/he knew the difference between the two, or even that there was a difference between the two. That always seems to be a common misconception about Excel/computers.

 

[The Villan]

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

 

[Zack Barresse]

Perfectly.

Btw, hope you had a good new years Villan.

 

[The Villan]

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 ?

 

[Zack Barresse]

Yes, very good one for me. Spent with the family. And I didn't even make it to one! LOL!

Okay DrBob, I'll try not to hijack your thread anymore.

 

[DrBob]

Hi there again everyone ) I managed to with help here and learning about the functions I managed to get this paper done Glad theres a site for things like this don't know what I would have done otherwise.

and as for the query about whether or not I am allowed to 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.

 

[The Villan]

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.