Formula to calculate % of income to transfer to specific saving target

[gko_87]

In the workbook attached, sheet one contains data for income & expenditure while sheet two contains saving targets.

REQUIRED:
I would like to be able to calculate the percentage of income (income in Cell D4) to allocate each saving target so that each of the targets is reached within the time stipulated.

Attached is the workbook:

% of income.xlsx

[JohnTopley]

Try

=$C5/SUMIFS('Income&Expenditure'!D:D,'Income&Expenditure'!B:B,">=" &'Income&Expenditure'!B8,'Income&Expenditure'!B:B,"<=" &'Income&Expenditure'!B8+'Saving Targets'!D5*7-1)

This SUMS the income over period of weeks in D5 (assuming start data is 01/01/2018)

Obviously it takes no account of inflation/salary increases/cost increases.

[JohnTopley]

Alternatively, take a weeks salary , multiple by number of weeks.

so = savings/(weekly salary * number of weeks)

[gko_87]

It is the income in cell D4 in the income&expenditure sheet that is to be considered.

Just tried the formula in cell E5 of the savings sheet. copying the formula down produces the #DIV/0! error in some cells. Some of the % are also over 100%.

See attached.

Copy of % of income.xlsx

[JohnTopley]

Try this

in F5 Calculates the savings amount in a 4 week period

=C5/D5*4

in G5 (estimate of monthly salary: only approximate as salary may be 4/5 week periods)

=F5/'Income&Expenditure'!$J$8


in the first post you only had a month's daily figures so the SUMIF will obviously be wrong)

[gko_87]

With the above formulae, the sum of all values in column F is 202,209. This is less than the shared figure in cell J8 of the Income&Expenditure sheet which is 99,400.

Shouldn't the sum of all these values (in column F of the Savings sheet) take us back to the originally shared figure (99,400 in cell J8 in the income&expenditure sheet?

[JohnTopley]

No: it simply means you are living well beyond your means!!!

Look at buying a car:

1,500,000 over 49 months (@4 weeks per month) = 30,612 per month (30% of monthly salary of 99,400)

Very basic maths.