Results 1 to 4 of 4

Thread: Equalising formula

  1. #1

    Equalising formula



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am getting a major headache trying to think of a formula to make desired calculations and may come to the conclusion that one does not exist. Basically I want a formula that will calculate the split in investments required to provide an equal income for 2 people. The income will also be dependent on an income rate, which in turn is dependent on the age of each person. Not sure how else to explain it but I've attached a spreadsheet to help in this. Sheet 1 titled 'Income Rates' shows the income rate applicable to specific ages. Sheet 2 entitled 'Investment Splits' is where I am coming up with my problem. I've created a vlookup formula in column G so that the appropriate income rate is derived from the previous sheet based on a specific age. Now I'd like a formula that can calculate a split percentage in cells H3 & H5 in order to create an equal income for a couple in cells I3 & I5, based on the total value of investment and the appropriate income rate.

    Am I searching for a formula that doesn't exist??
    Attached Files Attached Files

  2. #2
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Hi,
    plz see the file. is this help ?
    Attached Files Attached Files

  3. #3
    For some reason it won't let me open the file. It's telling my it's a php file??

  4. #4
    Conjurer sambit's Avatar
    Join Date
    Sep 2014
    Posts
    143
    Articles
    0
    Excel Version
    Excel2007
    Try in Cell H3

    =$B$6*HARMEAN($G$3,$G$5)/2/G3/$B$6

    and in cell H5

    =$B$6*HARMEAN($G$3,$G$5)/2/G5/$B$6
    Attached Files Attached Files

Posting Permissions

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