Google Sheet - Compound FV on a SPECIFIC DATE

Question

I got a basic issue looking like voodoo magic to me (a noob at google sheet):

What I need (a future value on a specific date)

Instead of the usual Future Value after "n" years or months I need to know the future value on a SPECIFIC DATE (eg.: on the 20th of March 2030 or in "2560" days) compounded either yearly or monthly with or without contribution.

What I have (the usual data for calculating FV):

• Yearly (or monthly if it's easier) compound rate.
• A present principal which compounds yearly or monthly
• A regular monthly (or weekly) contribution to the principal.

SAMPLE FORMULAS I WORK WITH:

FV = SV*(((CAGR*100)/100)+1)^n.

FV - Future Value SV - Starting Value CAGR - Compound Annual Growth Rate n - years

This tells me how much capital I will have given an annual growth rate after n years. But how to have the formula telling me what that capital will be on a specific date and also how to add the monthly/weekly contribution?

Any idea on how to achieve this? Thanks a lot

Answer 1

I created a calculator in Google Sheets that has all the necessary formulas for FV, PV, PVAF, and "Compound FV on a SPECIFIC DATE".

Although, I believe this question is more suited to the Personal Finance & Money and Stackexchange site.

See this link for the calculator.

Description

In order to calculate the contributions into the formula we must use the following values:

p = initial value
n = compounding periods per year
r = nominal interest rate, compounded n times per year
i = periodic interest rate = r/n
y = number of years
t = number of compounding periods = n*y
d = periodic deposit

The formula for the future value of an annuity due is

d*(((1 + i)^t - 1)/i)*(1 + i)

(In an annuity due, a deposit is made at the beginning of a period and the interest is received at the end of the period. This is in contrast to an ordinary annuity, where a payment is made at the end of a period.)

The formula is derived, by induction, from the summation of the future values of every deposit.

The initial value, with interest accumulated for all periods, can simply be added.

pfv = p*(1 + i)^t

total = pfv + fv

So the overall formula is