+ Reply to Thread
Results 1 to 7 of 7

annual part price reduction formula

  1. #1
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Question annual part price reduction formula

    If I am buying Parts and the supplier gives me 3% reduction of that part each year for the next three years.
    part initial price is $100

    Also what if some parts get 1.5% reduction
    Also what is some parts get a reduction for 4 years instead of 3.

    Question: Is there a formula that i could drag down to find out the final price of each part.

    Part A $100 3% 3% 3%
    Part B $100 1.5% 1.5% 1.5% 1.5%

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: annual part price reduction formula

    See attached.
    Attached Files Attached Files
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Re: annual part price reduction formula

    Thank you for responding but your response wasn't helpful.
    I will add a photo to better illustrate.
    reduction.PNG

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: annual part price reduction formula

    Ok. Changing to your presentation and getting the same results. See attached.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-30-2014
    Posts
    3

    Re: annual part price reduction formula

    Thank you, Do you know if there is a pre-made formula that deals with situations like this? Would it be a PRICE or Depreciation formula?

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: annual part price reduction formula

    Don't believe that function exists. However a User Defined Function (UDF) could be created in VBA.

    You could copy and paste this as instructed below

    Please Login or Register  to view this content.
    Open your VBE by Alt +F11
    Click on Insert --> Module
    Paste this code into the window that opens on the right
    Save it.
    Close the VBE

    Now with your spreadsheet open, click on the fx Function Address
    Search for UDF and select Discount.
    Complete the form as you would for any other function.

    Oh, importantly, if you save this in the workbook, you must save the workbook as an .xlsm file if you are using 2007 or later.
    Last edited by alansidman; 05-30-2014 at 04:58 PM.

  7. #7
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: annual part price reduction formula

    Maybe ...

    A
    B
    C
    D
    E
    F
    G
    H
    1
    Part
    Initial
    Year1
    Year2
    Year3
    Year4
    Final
    2
    A
    $100.00
    3.0%
    5.0%
    2.0%
    $ 90.31
    G2: =B2*PRODUCT(INDEX(1-C2:F2, 0))
    3
    B
    $100.00
    1.5%
    2.0%
    1.6%
    3.0%
    $ 92.14
    Entia non sunt multiplicanda sine necessitate

+ 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. Formula Reduction
    By Journeyman3000 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-17-2013, 06:24 PM
  2. Replies: 4
    Last Post: 04-27-2011, 08:55 AM
  3. Referncing a price to get the part
    By JustCofox in forum Excel General
    Replies: 5
    Last Post: 05-20-2007, 08:05 PM
  4. annual increases in price lists
    By smedegaard in forum Excel General
    Replies: 1
    Last Post: 02-17-2006, 09:00 AM
  5. Find Part that Has Price, and Retrieve Value
    By christopher.sul in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-03-2005, 02:13 PM

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