Financial Planning

frankday

Board Regular
Joined
Apr 13, 2012
Messages
94
I am working on a 30-year plan. I want to have a row of years and the amount that would have to be put away each year. Lets say we had a $200,000 project that we wanted to do in year 2025 and we wanted to save for 3 years. 2019 would be 0, 2020 would be 0, 2021 would be 0 , 2022 would be one third of $200,000 and so on until year 2025 when the project would be completed. I know there is a formula for each years cell but I can't think of it.

Any help would be appreciated.

 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
Like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">Cost of project:</td><td style="text-align: right;;">£300,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;;">Completion year:</td><td style="text-align: right;;">2025</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="font-weight: bold;;">Number of years to save:</td><td style="text-align: right;;">3</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="font-weight: bold;;">Year</td><td style="font-weight: bold;;">Amount to save</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">2018</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">2019</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">2020</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">2021</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">2022</td><td style="text-align: right;;">£100,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">2023</td><td style="text-align: right;;">£100,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">2024</td><td style="text-align: right;;">£100,000</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">2025</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">2026</td><td style=";"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">2027</td><td style=";"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B6</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$A6>=$B$2-$B$3,$A6<$B$2</font>),$B$1/$B$3,""</font>)</td></tr></tbody></table></td></tr></table><br />

Copy B6 down as necessary.

WBD
 

Drakefo71

New Member
Joined
Aug 22, 2018
Messages
1
Financial advice to invest and save in higher return funds is very important. Hopefully I will succeed in my personal financial planning as have started learning things and concept of my own as could never trust someone else advice.<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,962
Messages
5,471,732
Members
406,779
Latest member
a_faulding

This Week's Hot Topics

Top