Determine number of currency bills or notes for any amount – Currency bill Calculator [How To]

5
6688

Here is the situation, you are working somewhere and handling cash every second. You have to manually determine how much number of bills of each denomination you need to payout certain amount of cash. Instead of damaging your brain cells doing repetitive task, why not get Excel help to confirm and increase accuracy? Following is the solution:

currency bill calculator using excel 1

This is exactly what we are learning today!

Building currency bill calculator – Step by step

Step 1: First determine the currency for which you want to make a bill calculator. Mine is Pakistani rupee. Pakistani currency have following denominations:

  • 5000
  • 1000
  • 500
  • 100
  • 50
  • 20
  • 10
  • 5
  • 2
  • 1

I listed them in a column. Give this column a particular heading. Give heading to two next columns as “# of bills” and “Amount” or whatever you desire. This is what I had once done with this step:

currency bill calculator 1

Step 2: I have mentioned in cell E6. I named this cell as “amount” by typing in name box directly and hitting enter. It will make writing formula easy for me.

currency bill calculator using excel 2

Step 3: In first cell of “# of bill column” put this formula:

=ROUNDDOWN(amount/D9,0)

currency bill calculator using excel 3

So what is happening here. Taken the amount mentioned in cell, divided by the first denomination and rounding it down to whole number. Actual catch is to understand the use of ROUNDDOWN and not ROUND function as we need to bring down the result any excess amount will be dealt with smaller denominations.

For example I have 76767 mentioned in amount then dividing it by 5000 will give 15.3534. Rounding down will give 15.

Step 4: In the amount column, simply multiply the # of bills and denomination to calculate the total amount with the following formula:

E9*D9

Step 5: In the second cell of “# of bills” column put this formula:

=ROUNDDOWN((amount-SUM($F$9:F9))/D10,0)

currency bill calculator using excel 4

Don’t get overwhelmed by the length of formula. Concept is fairly easy. Out of the total amount mentioned, amount already covered by first (5000) denomination is being deducted. The result of which is then divided over the second denomination i.e. 1000 in our case.

Continuing our example of 76767, 75,000 is already covered by 5000 currency bills. Therefore, remaining amount is 1767. Dividing 1767 over 1000 will give 1.767 and rounding it down will result in “1”.

Step 6: Select the fill handle of # of bills column and drag it down to fill the range and also do the same with amount column so that cells are populated with appropriate formula.

currency bill calculator using excel 5

Your bill calculator is ready! Here it is once again:

currency bill calculator using excel 1

Easy as cake!

Global Currency bill generator/calculator
Taking this concept further, I made a global currency bill calculator that can take any currency of any country. Salient features are:

  1. Easily select the country from the drop down to automatically get the appropriate denominations of that country
  2. Fully customizable, you can add your own currencies and particular denominations in the calculator by populating the denomination table
  3. Completely dynamic, calculator manages its shape automatically as denomination list changes from one country to another, it does not loses its cool!

Have a look at the following figure where I populate the currency table with new currency of Azerbaijan and it determines the currency bills automatically in a zing!

global currency bill calculator premium

Other notable takeaways from the file include:

  • Application of structured referencing system
  • Dynamic Data validation list
  • OFFSET function application
  • And many other hidden bits!
Download Now

5 COMMENTS

  1. Hi, wondering if you can help with my situation. Trying to determine start up cash for a fundraiser. assuming we have a bank of $4000, what’s the best way to break this money out into various denominations? Our items for sale are all priced in multiples of $5. Is there a logical formula for this or do folks normally just wing it?

  2. Any Update Regarding Mr. John’s request, I’m Also looking an excel file for this Task, I’m Working in a NGO, we have to generate a report how much charity we have gathered and tally it with cash in hand, there should be an option to input how much Totals of each denomination we have than it should calculate it accordingly.

    • Your requirement is a little different one from John’s. What you are looking for is the reverse of this template. I will try to do it asap. Thanks for visiting and giving confidence!

  3. This is awesome, my only issue is trying to use this example but letting the formula know that I only have a limited amount of each denomination, for example spreadsheet tells me I need 10 (£50 denominations) but I only have 6, is there an easy way to format this into the equation?

    • Oh that is really an interesting extension to this situation 🙂

      See if we discuss we learn new things. I will try to write a follow up as P.S to the article this Sunday possibly.

      Thanks again! Great idea!

LEAVE A REPLY