The Economic Times daily newspaper is available online now.

    Use this MS Excel tool to manage your financial goals

    Synopsis

    Goal Seek is an easy tool that helps investors modify calculations according to their requirements.

    financial-plan-gettyGetty Images
    MS Excel provides a number of functions that have major applications in personal finance.
    Personal finance management focuses on five core areas—income, spending, saving, investing and protection. Financial planning experts evaluate the risk and rewards associated with different investment options and guides individuals towards their desired goals, both short-and long-term. Short-term goals may include travel, home improvement and repayment of credit card debts, while long-term objectives include planning for children’s education, marriage and retirement.

    Managing personal finances involves a thorough understanding of the statistical and mathematical concepts that help assess the performance of various investment options. Calculations and number crunching are an integral part of the planning exercise. Planners have to take into account the varying income-expenditure patterns, age profiles and risk profiles of individual investors while devising investment plans.

    MS Excel provides a number of functions that have major applications in personal finance. Some core concepts that can be worked out using Excel include the time value of money, compounding and discounting. Excel functions come in handy when calculations involve an one-time investment or when the investments are spread over a period of time.

    The compound interest formula is FV=P ×(1+r)^n. There are four components in the equation. FV is the future value, P is principal amount, r is the interest rate and n is the tenure (in years). Using the above equation, Rs 10,000 (P) invested for 5 years (n) at a 7% interest rate (r), which is compounded annually will return an FV value of Rs 14,026.

    However, standard calculations do not always work. Certain adjustments are required to bring numbers in agreement with individual goals. Excel provides a useful tool—Goal Seek— which helps to make such modifications. It provides a simple user interface and needs only three inputs.

    Goal Seek helps to do a reverse calculation on the basis of a predefined objective and it can only be used on inbuilt or a direct formula. It helps to do a ‘What If’ analysis and works on a trial and error basis for back-solving the problem until it arrives at the answer. For the above example, Goal Seek can determine the number of years in which Rs 10,000 will turn into Rs 20,000 at the given 7% interest rate. Or how much money should be invested today to accumulate Rs 1 lakh after 5 years at 7% interest rate.

    Let us demonstrate the usage of Goal Seek using the compound interest example. We will try to find out the interest rate at which Rs 10,000 will turn into Rs 20,000 in 5 years. Goal Seek is located in the data tab and is available under the what-if analysis. A small dialog box will appear, as the user clicks on Goal Seek (see screenshot1).
    MS-Excel-1


    In screenshot1, Goal Seek is employed relative to a given formula (FV). One can either arrive at the value of 14,026 using the inbuilt ‘FV’ formula or use the compound interest formula in the Excel cell.

    There are three inputs. Set cell is the formula cell that a user needs to modify, in our example, it is FV value, which exists in B7 cell. The second input To value is the desired goal, which is Rs 20,000, double the principal amount. The third input ‘By changing cell’ is the response variable which will enable one to reach the goal. In this example, the response variable is the number of years, which exists in B4 cell. After the execution of Goal Seek, then will change to 10.24 and FV will change to 20,000, (see screenshot2).

    MS-Excel-2

    Therefore, it will take 10.2 years to double the investment amount, if invested at 7% interest rate compounding annually. Instead of number of years, if one wants to look at the interest rate that will double the amount in the original tenure of 5 years, then the ‘By changing cell’ will be B3 in the Goal Seek dialog box. Executing such query will return the output as 14.9%, that is, Rs 10,000 will grow to Rs 20,000 in 5 years, if invested at an annualised rate of 14.9%.

    Users can try out the amount needed to accumulate Rs 1 lakh at 7% rate in 5 years. The ‘To value’ will take the value of 100,000, the ‘By changing cell’ will be B2 and no change is needed in the ‘Set cell’. Execute the function to get the value of 71,299.

    The ‘set cell’ value needs to be derived either using the inbuilt Excel formula or a directly placed formula. The By changing cell should be an input in the formula. Else, the Goal Seek will not respond.

    (Your legal guide on estate planning, inheritance, will and more.)

    Download The Economic Times News App to get Daily Market Updates & Live Business News.

    ...more

    (Your legal guide on estate planning, inheritance, will and more.)

    Download The Economic Times News App to get Daily Market Updates & Live Business News.

    ...more
    The Economic Times

    Stories you might be interested in