Pro-business Excel VBA Programming

A Mutual Fund is an investment that you put your money in a big pool of money that is invested in different investment instruments in a diversify manner. I am not going to discuss in details about mutual fund. I assume that when you read this article, you are looking for a way to calculate how much you need to invest in a regular basis to attain your financial objectives. But in case you don’t know Mutual Fund, you can google it and you will see a lot of information in the internet.

Situation:
You are working hard and you have a few dollars in excess from your salary. You want to invest this money in a way that you don’t have to check it in a daily basis because you are busy with your job. You want to make sure that this money is invested wisely and you want someone to manage it for you so you can just check on it in a monthly or quarterly basis. Mutual Fund is ideal for this situation. Your objective is to know the amount of money to spare in a monthly basis to obtain your target specific amount of money in a specific time frame. Excel is the tool you need to forecast your Mutual Fund. This tool is useful when you want to buy a house and lot, or a car in the future.

Putting money in Mutual Fund in a regular basis is called Dollar-Cost Averaging. It is a smart investment strategy. This method works in your favor no matter what happens to the market. I will show you a way to forecast Mutual Fund Dollar-Cost Averaging, in case you want to do it.

Solution:
Copy the following screen grab in your Excel worksheet. Cells B3, B4 and B5 are the data entry fields for annual interest rate, number of investment years and the target amount respectively.















Cell B8 has the formula to compute for the amount to put monthly in Mutual Fund. The formula used the PMT function. The formula is as follow:

=-PMT(B3/12, B4*12, 0, B5)

First parameter, Interest Rate from Cell B3, is divided by the number of months per year, 12 months.

Second parameter, Number of Years, from Cell B4, is multiplied by 12 to determine the total number of months.

Third parameter is zero. The parameter is used for other function of PMT.
Fourth parameter has the target amount.

(Please see PMT Function)

Maybe you are thinking that interest rate is not easy to come up with unlike the number of years and target amount. To bring about the correct estimate of interest rate, you need to choose the Mutual Fund that you want to put your money with. Do a research about its performance from the business newspapers and financial websites. Let’s say, you are planning to invest for the next 5 years and your target amount is $500K. Look for the result percentage of the 5-year performance of the Mutual Fund and use that as the basis for your interest rate. Please note that past performance is not a guarantee of future results, however it is a good estimate. If, for example, the interest rate you’ve got is 8.5%, let’s enter the 3 parameters to B3, B4 and B5. The result is you have to spare $6,716.60 per month for 5 years, as shown below:

This function returns a Double Data-Type specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate. Syntax is as follows:


PMT(rate, nper, pv, fv, type) As Double


Parameters

Rate - A required parameter specifying interest rate per period.

NPer - A required parameter specifying total number of payment periods in the annuity.

PV – A required parameter specifying present value that a series of payments to be paid in the future is worth now.

FV – An optional parameter specifying future value or cash balance you want after you've made the final payment.

Due – An optional parameter. Type of payment due date that specifies when payments are due. Valied values are: 0 or omitted means payments are due at the end of the period, 1 means payments are due at the beginning of the period