How to automatically calculate payment_amount in the FV formula?

Question

I'm trying to calculate the payment_amount in the FV formula so I could plot the growth chart over certain period. The difference is that I know what FV I want to reach, and I know the rate and number_of_periods.

I created this example to illustrate the problem:

https://docs.google.com/spreadsheets/d/15anlU1i9XWAgI-J-6lFmWSsLRgOck_iZGitKfPKmEt4/edit?usp=sharing

You can see two lines in the chart.

The purple line is a simple straight line from $0 to $1.5M. The data is coming from the FV formula in the column B.

The green line is more what I'm looking for. The data is coming from the FV formula in the column C, but I had to manually input the payment_amount value.

Is there a way to automatically calculate the payment_value based on the rate and the number_of_periods?

Answer 1

Is not as exact as your value but you can use:

=-FV(F8, 1, -PMT(F8,F11,1, F5), 0)

I stated F11 as number of periods in case you want to change it dinamically. You can also use this sequence to have the full calculations in one cell:

=Scan(0, SEQUENCE (F11),LAMBDA(ag,v,-FV(F8, 1, -PMT(F8,F11,1, F5), ag)))