1. Home
  2. General Accounting
  3. Please help with a discount formula - Excel 2003

Please help with a discount formula - Excel 2003

Would sks please help me with a formula for the figures below

I have to write the formula that calculates the Annual Percentage Interest

Cost of the Discount not taken Annual Percentage Interest Cost of Discount Not Taken

Prompt Payment Discount Amount 2.5% (cell A1)

Invoice has to be paid within how many days to qualify for the discount

10 (cell A2)

Net amount of invoice to be paid within how many days 30 (cell A3)

Annual Percentage Interest Cost of Discount Not Taken ? (cell A4) answer

The figures have to be worked out on $100,000. I assume the days forgone would be 20 (30 - 10)

I know the formula goes something like this -

Annual % interest cost of discount not taken = % prompt payment discount / (100% - % prompt payment discount) x 365 / credit days forgone

But I cannot get my head around it. Some advice would be most appreciated. Regards Chris.

Reply to
AussieGal
Loading thread data ...

Is this an assignment for school?

Wayne Brasch

Reply to
Wayne Brasch

"Wayne Brasch"

appreciated.

it's obviously a school problem but at least "AussieGal" has given a tentative formula -

which seems to be wrong but the "Cost of Discount Not Taken" is misleading, if you take the discount of 2.5% for 10 days the cost is XXX - (a very high interest rate!) but if you don't take it the cost is simply the regular interest rate.

Reply to
John

Wayne and I suspect that this is a school assignment. Two questions within a couple of days, identically worded except for the formula that you "cannot get [your] head around". Many of us here think that students should do their own homework - and be honest if you MUST ask for help.

discount

discount /

appreciated.

Reply to
!-!

Hi Wayne and !-!, you are right in a way. No it is not a school assignment, yes it is an assignment. I have been offered a job which includes 'experience' in excel, I dont have much experience so I lied to get the job and got it. It is in the hospitality business and they have a lot of reports to the owners. I have set myself on a learning curve with a paper borrowed from a friend of mine at college and most of it I can get, but this one really stumped me. After the help I received on the other one I thought I was a bit too clever when I could see the answer after your help. So I am sorry if I offended. Regards Chris.

Reply to
AussieGal

Well, Chris, since you have confessed your trick let's see if I can be of help to you. The formula may be something like this: There are to be considered 360 days in a business year for this calculation purpose. The formula, then, would be 360 days / 20 days x 2.5. The 20 days are as you have said 30-10.

You have the right idea. Now go and put this into Excel and see what you get.

Wayne Brasch

Reply to
Wayne Brasch

Hi Wayne, you are very kind. What I should have said in my question was that I actually had the answer at approximately 46.79% by working it out on a piece of paper and with a calculator (365 days). I really wanted to know was how to get it into Excel without anyone realising I am not as experienced as I said. I think I could have got the answer with a formula a mile long but the way you have shown me is terrific and proved that I was on the right track Once I saw you write it that way it was easy, btw the answer is really

45%. You are an angel and thank you for your kindness. I start on Monday week, so I am really burning the midnight oil.

Kind regards Chris.

To reply personally take out the .au from hotmail.

formatting link

Reply to
AussieGal

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.