Investment planning – Excel kitchenette

Excel has a multitude of financial functions. I’m not sure how many of them can be applied in Serbian business environment, but they are useful when making complex calculations. Financial investment planning functions aim to answer the question: how much will, under the given conditions, be worth my money after a certain period of time? That is, if I borrow money to someone how much I will come? Or, if we lend money for how much it will cost me?

In order to reach the answer to the above questions we will use several financial functions, but for the beginning let’s explain some crucial terms:

Present Value represents the money that we borrowed to someone or invested, or the money that we borrowed from someone. When dealing with the present value, it is important to know that, when we give money, the value of the amount in front must have a minus sign. That is, the amount we give must be a negative number. When we receive money, the received amount should be a positive number.
Number of periods represents the investment period. If interest rate is monthly, number of periods should be multiplied with 12.
Rate is interest rate for given period. This can also be the expected rate of return on investment. If the number of periods is expressed in months, the rate should be divided by 12.
Payment) represents the amount of debt repayment at the end of a period or the beginning of a new one.
Future Value is income or expense after expiration of the specified number of periods.

To calculate the future value, the FV function is used, whose syntax is:

FV(<rate>,<number of periods>,<payment> ,[<present value>],[<calculation type>])

We have already explained all the arguments, but it is only necessary to emphasize that the type of calculation can be used to determine whether the payment of principal is made at the end or at the beginning of the new period. Let’s see how this works for example! Enter the formula:

=FV(0.05,1,0,-1000)

This formula calculates how much money we will get after one year, if we invest 1000 € at 5% interest rate. This formula shows the sum of principal and interest. If we want to see only interest, we need to input the value of the principal as the third parameter:

=FV(0.05,1,1000,-1000)

Imagine that we negotiated a more favorable arrangement, so we calculate the interest on a monthly basis. Then the formula would look like this:

=FV(0.05,12,0,-1000)

Let’s assume that we borrowed money, at an interest rate of 3%, to 5 years. How much money should we return? Enter the formula:

=FV(0.03,5,0,1000)

If we have decided to pay 200 € each year, paying by the end of the period, the formula would look like this:

=FV(0.03,5,-200,1000,0)

If we were to pay after the end of the period, the formula would look like this:

=FV(0.03,5,-200,1000,0)

The difference between these two formulas is that we make the first payment at the end of the first year, so the interest is calculated at (1000-200) €. If we pay at the beginning of next year, we pay interest on the full amount of principal, and it is reduced for payment only in the next period.

In addition to the FV function, we have complementary functions that allow us to calculate some of the parameters if a future value is known. Their syntaxes are:

PV(<rate>,<number of periods>,<payment> ,[<future value>],[<calculation type>])

RATE(<number of periods>,<payment> ,<present value>,[<future value>],[<calculation type>],[<estimate>])

NPER(<rate>,<payment> ,<present value>,[<future value>],[<calculation type>]

PMT (<rate>,<number of periods> ,<present value>,[<future value>],[<calculation type>]

There is a parameter called estimate in the RATE function. It refers to an initial estimate of the amount of interest rates to be. If not specified, Excel means a rate of 10%. This parameter is not required, it only accelerates the calculation process when it comes to complex calculations.