Systematic investment plan interest calculation

You are dealing with regular, periodic payments. There are formulas in the source you cite for this situation, but none of those formulas will allow you to find the interest rate necessary to turn a series of regular payments into a specified final amount. The only way is to use trial and error; trying different interest rates with the given payment information until you find the one producing the desires amount.

Fortunately, many online calculators are available to carry out these trial and error calculations. More simply, Excel supplies a function, RATE, that will give the interest rate for a given series of payments.

The first step is to write the final amount in terms of the interest rate. This is a geometric series, so we can use the corresponding formulae. It's not exactly clear what the situation is, but if you're depositing at the end of each month, and getting your money back at the end of the 60th month (in which case the last payment is immediately returned), you have

sum k goes from 0 to 59 of (1000*r**k)

If you have to wait a month after the last payment to get your money back, then it would be

sum k goes from 0 to 59 of r*(1000*r**k)

If you pay at the beginning of each month, and get the money back at the end of the 60th month, then it's

sum k goes from 1 to 60 of (1000*r**k)

(Note: you used r for the interest rate. I believe that usually i is used for interest, and r is used for the quantity 1+i, which is how I am using it. I'm also using it to refer to the monthly rate.)

In Excel, you could fill 0 cells with that formula, take the sum, and then use goal seek to find r such that the sum is 75000. In R, the formula is

T = 1000*sum(r**(0:59))

You can define a function that returns how far you are from your goal of 75000:

T <- function(r){1000*sum(r**(0:59))-75000}

Then you can use a root-finding function, for instance uniroot. To use uniroot, you need a guess as to something you think is less than the desired number, and a guess that's above. Clearly, you're getting some interest, so r is above 1, and you're not doubling your number, so it looks like it's below 2. So I'll use those as lower and upper bounds.

monthly_r = uniroot(T, lower = 1, upper = 2)

This gives 1.007327. This, again, is the monthly rate. If you raise that to the twelfth power, you get 1.09155. This gives 9.155% as the APY. Expressed as APR compounded monthly, it's 8.792%. As force of interest, it's 8.760%.

This can be put in closed form as 75000 = 1000*(1-r^60)/(1-r). This gives r^60-75*r+74000 = 0. You can use a polynomial solver to solve for r.

You will most probably do this in excel I think so there you go:

=POWER(RATE(12*5,1000,0,-75000,1)+1,12)-1

where,

12      - is the no of months in a year
5       - is the no of years
-75,000 - is the amount returned (negative sign implies cash inflow to you)
1       - the last argument for RATE() is the specify investment is at the start of month

Manual calculation require you to solve for r in the following equation:

A(1+r)^n - r*FV - 1 = 0

where,

A - is the monthly payment
FV - is the future value of the investment
n - is the number of periods, in this case 60 (12 months x 5 years)
r - is the monthly interest rate

To convert r into CAGR (annual rate) do this: (1+r)^12-1