+ Reply to Thread
Results 1 to 7 of 7

Price Calculation - mathematical formula

  1. #1
    Registered User
    Join Date
    12-07-2018
    Location
    United Kingdom
    MS-Off Ver
    365
    Posts
    4

    Price Calculation - mathematical formula

    Hi,

    I may struggle to explain this, so I am sorry if it gets confusing!

    I am trying to calculate an items selling price but have hit a problem. Basically I have calculated a selling price of £1,209.56 but I now need to add a payment processing fee of 3.4% of the total. Adding the 3.4% to the selling price gives me £1,250.69. The problem is that by adding the 3.4% (£41.13) to the selling price, I have increased the amount that the 3.4% is based on, which means that the 3.4% is now actually going to be around £43.52, meaning I haven't fully covered the cost.

    I know I could just up it a few points of a percent to comfortably cover the cost but I would like to know if there is some mathematical formula or method that could calculate this more precisely

    Thanks for any help

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Price Calculation - mathematical formula

    Clear ... duplicated. See next post.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Price Calculation - mathematical formula

    The problem is that by adding the 3.4% (£41.13) to the selling price, I have increased the amount that the 3.4% is based on, which means that the 3.4% is now actually going to be around £43.52, meaning I haven't fully covered the cost.
    I'm confused. Are you saying you want to add a processing payment to the processing payment? How is that increasing your cost of sale?

    You calculate the selling price. You calculate the processing fee. You add them ogether. That's what you charge the customer. End

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Price Calculation - mathematical formula

    If I understand what you are describing it looks like:

    s is selling price = 1209.56
    t is total price =x (unkown)
    r is processing fee rate (0.034)
    basic algebra statement: t=s+r*t
    substitute: x=1209.56+0.034*x
    solve for x
    The only "tricky" part is that x is on both sides of the equation, so the first step will be to get all x's on the same side. I will refer you to this algebra tutorial (https://www.purplemath.com/modules/solvelin3.htm ) that talks about solving equations where x appears multiple times in the same statement. It should not be overly difficult to solve your problem from there, then program the result into Excel (let us know if you get stuck).
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    12-07-2018
    Location
    United Kingdom
    MS-Off Ver
    365
    Posts
    4

    Re: Price Calculation - mathematical formula

    Hi,

    Thanks for your reply. it is rather hard to explain but the problem is that the payment processing fee is charged by PayPal on the total sale. So, to breakdown a simpler example:

    Wholesale price: £200
    Delivery to me: £10
    Target profit: £10

    Selling price would be £220

    But then PayPal would charge 3.4% to process the transaction (£7.48), meaning my profit would only be £2.52.

    Now if I was to add £7.48 to the selling price setting it at £227.48, PayPal would charge their 3.4% on a sale but this time the fee will be larger (£7.73), meaning my profit would be £9.75.

    I know this is getting into small differences but would like to know the correct way of doing this

  6. #6
    Registered User
    Join Date
    12-07-2018
    Location
    United Kingdom
    MS-Off Ver
    365
    Posts
    4

    Re: Price Calculation - mathematical formula

    Thanks for the link, I will have a read through

  7. #7
    Registered User
    Join Date
    12-07-2018
    Location
    United Kingdom
    MS-Off Ver
    365
    Posts
    4

    Re: Price Calculation - mathematical formula

    Guys, you are amazing and that link helped a lot. My formula could be simplified down to my original selling price divided by 0.966!

    So for my second example: £220 / 0.966 = £227.74327...

    Selling Price set to £227.74327...
    PayPal fee becomes: £7.74327...

    Selling price - PayPal fee = £220, so the rest works. Obviously will need to adjust for the decimals but very happy with this

    Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 3
    Last Post: 12-08-2016, 10:37 AM
  2. Multi -array / -table Price Calculation Formula
    By carlyman in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-03-2016, 05:20 PM
  3. Replies: 2
    Last Post: 06-15-2015, 01:48 AM
  4. Replies: 9
    Last Post: 11-25-2014, 06:07 PM
  5. mathematical and calculation formula for angles
    By maestro_uk in forum Excel General
    Replies: 6
    Last Post: 08-09-2014, 07:22 PM
  6. Replies: 3
    Last Post: 03-05-2013, 03:42 PM
  7. Price function difference in Output formula vis a vis Manual Calculation
    By abhi_23 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-17-2006, 03:57 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1