A formula to spread values across 16 weeks totaling 508. Increasing by week

[astange]

A formula to spread values across 16 weeks totaling 508. Increasing from week 1 thru week 16.
I Also need to calculate the cumulative % over that period. In The Overall Planning Section.

[Bernie Deitrick]

Use a formula like

=PPMT(1%,ROW(A1),16,-508)

Copied down for 15 more rows (or change ROW to COLUMN and copy across for 15 columns). Change the 1% to your desired rate - the total of the 16 cells will always be 508...

[astange]

I tried it with =PPMT(.05,COLUMN(D3),19,-508), this gives me a sum of 456
My Columns Range is (D3:S3)= 16 weeks totaling 508

[Bernie Deitrick]

Column returns the column number, so you need to start with 1 (column A) and use 16 not 19:

=PPMT(.05,COLUMN(A1),16,-508)

Your formula would return the sum of principal payments 4 through 19 - so it would always be less than 508 because it misses payments 1, 2, and 3.

[Melvosh]

You have the weeks listed in row 2, so this might work:

Please Login or Register  to view this content.

[astange]

thanks for all your help Bernie

[astange]

You are awesome Melvosh, worked perfectly.

[Melvosh]

Happy to help