I am working on a depreciation schedule as seen in the screen shot below:
This depreciation schedule spans 120 monthly periods. The monthly ending periods are listed in row form from N15:EC15. The matching monthly ending periods are listed in column form from M37:M156.
The formula area ranges from N37:EC156.
What I would like to do is create a formula that I can copy and paste for the entire area from N37:EC156 rather than changing my formula for each row. The formulas I have listed below are working but I would have to copy them and change them over the entire 120 periods, which will work, but will take a long time. I think something along the following guidelines would work quickly where I could just use one formula for the whole area:
• Let’s use row 38 as an example to start:
• If the period in O15 >= the period in M38, then count the columns from $O17:O17, otherwise leave “Blank”. So cell N:38 would be blank;
• If the count of those columns is <=$L$25, then ($O30*O$26);
• If the count of those columns is >$L$25, then 0
The following formulas demonstrate how I am currently copying, pasting and editing for each row rather than using one formula for the entire area.
Cell O38 =IF(COUNT($O17:O17)<=$L$25,($O30*O$26)*-1,0)
Copy Cell O38 and Paste from Cell P38:EC38. The following are the results:
Cell P38 =IF(COUNT($O17:P17)<=$L$25,($O30*P$26)*-1,0)
Cell Q38 =IF(COUNT($O17:Q17)<=$L$25,($O30*Q$26)*-1,0)…
Copy Cell P38 and paste to Cell P39:
Cell P39 =IF(COUNT($O18:P18)<=$L$25,($O31*P$26)*-1,0)
Change the following:
Cell P39 =IF(COUNT($P17:P17)<=$L$25,($P30*P$26)*-1,0)
Copy Cell P39 and Paste from Q39:EC39
Cell Q39 =IF(COUNT($P17:Q17)<=$L$25,($P30*Q$26)*-1,0)
Cell R39 =IF(COUNT($P17:R17)<=$L$25,($P30*R$26)*-1,0)…
| Excel 2010 | |||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| G | H | I | J | K | L | M | N | O | P | Q | |||
| 9 | |||||||||||||
| 10 | Depreciation schedule | ||||||||||||
| 11 | All figures in $ unless otherwise indicated | ||||||||||||
| 12 | |||||||||||||
| 13 | Operation date | 1/1/2016 | |||||||||||
| 14 | Period Start Date | 1/1/2016 | 2/1/2016 | 3/1/2016 | 4/1/2016 | ||||||||
| 15 | Period End Date | 1/31/2016 | 2/29/2016 | 3/31/2016 | 4/30/2016 | ||||||||
| 16 | Days | 31 | 29 | 31 | 30 | ||||||||
| 17 | Project Periods | 1 | 2 | 3 | 4 | ||||||||
| 18 | Percentage of total year | 8% | 8% | 8% | 8% | ||||||||
| 19 | Year | 2016 | 2016 | 2016 | 2016 | ||||||||
| 20 | |||||||||||||
| 21 | Tangible asset depreciation | ||||||||||||
| 22 | |||||||||||||
| 23 | Straight-line depreciation: | ||||||||||||
| 24 | Term (years) | 5 | |||||||||||
| 25 | Term (months) | 60 | |||||||||||
| 26 | Monthly depreciation rate | 1.67% | 1.67% | 1.67% | 1.67% | ||||||||
| 27 | |||||||||||||
| 28 | |||||||||||||
| 29 | Beginning Net Fixed Assets | From Balance Sheet (Beg Period) = | 1,456.0 | 1,456.0 | 1,472.7 | 1,488.7 | 1,504.1 | ||||||
| 30 | CAPEX | Per year = | 500.0 | 41.7 | 41.7 | 41.7 | 41.7 | ||||||
| 31 | DEPEX | (25.0) | (25.7) | (26.4) | (27.0) | ||||||||
| 32 | Ending Net Fixed Assets | 1,472.7 | 1,488.7 | 1,504.1 | 1,518.7 | ||||||||
| 33 | |||||||||||||
| 34 | Existing asset depreciation | (24.3) | (24.3) | (24.3) | (24.3) | ||||||||
| 35 | |||||||||||||
| 36 | Ongoing CAPEX depreciation | YE Costs | |||||||||||
| 37 | 1/31/2016 | (0.7) | (0.7) | (0.7) | (0.7) | ||||||||
| 38 | 2/29/2016 | (0.7) | (0.7) | (0.7) | |||||||||
| 39 | 3/31/2016 | (0.7) | (0.7) | ||||||||||
| 40 | 4/30/2016 | (0.7) | |||||||||||
Depreciation schedule | |||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| O14 | =N15+1 | |
| O15 | =EOMONTH(O14,0) | |
| O16 | =O15-O14+1 | |
| O17 | =N17+1 | |
| O18 | =YEARFRAC(O14,O15+1) | |
| O19 | =YEAR(O14) | |
| O26 | =(1/$L$25) | |
| O29 | =N32 | |
| O30 | =$L$30/12 | |
| O31 | =O158 | |
| O32 | =SUM(O29:O31) | |
| O34 | =IF(O17<=$L$25,-$L29*O26,0) | |
| O37 | =IF(O17<=$L$25,$N30*O26*-1,0) | |
| O38 | =IF(COUNT($O17:O17)<=$L$25,($O30*O26)*-1,0) | |
| P14 | =O15+1 | |
| P15 | =EOMONTH(P14,0) | |
| P16 | =P15-P14+1 | |
| P17 | =O17+1 | |
| P18 | =YEARFRAC(P14,P15+1) | |
| P19 | =YEAR(P14) | |
| P26 | =(1/$L$25) | |
| P29 | =O32 | |
| P30 | =$L$30/12 | |
| P31 | =P158 | |
| P32 | =SUM(P29:P31) | |
| P34 | =IF(P17<=$L$25,-$L29*P26,0) | |
| P37 | =IF(P17<=$L$25,$N30*P26*-1,0) | |
| P38 | =IF(COUNT($O17:P17)<=$L$25,($O30*P26)*-1,0) | |
| P39 | =IF(COUNT($P17:P17)<=$L$25,($P30*P$26)*-1,0) | |
| Q14 | =P15+1 | |
| Q15 | =EOMONTH(Q14,0) | |
| Q16 | =Q15-Q14+1 | |
| Q17 | =P17+1 | |
| Q18 | =YEARFRAC(Q14,Q15+1) | |
| Q19 | =YEAR(Q14) | |
| Q26 | =(1/$L$25) | |
| Q29 | =P32 | |
| Q30 | =$L$30/12 | |
| Q31 | =Q158 | |
| Q32 | =SUM(Q29:Q31) | |
| Q34 | =IF(Q17<=$L$25,-$L29*Q26,0) | |
| Q37 | =IF(Q17<=$L$25,$N30*Q26*-1,0) | |
| Q38 | =IF(COUNT($O17:Q17)<=$L$25,($O30*Q26)*-1,0) | |
| Q39 | =IF(COUNT($P17:Q17)<=$L$25,($P30*Q$26)*-1,0) | |
| Q40 | =IF(COUNT($Q17:Q17)<=$L$25,($Q30*Q$26)*-1,0) | |
| N13 | =Assumptions!H8 | |
| N14 | =N13 | |
| N15 | =EOMONTH(N14,0) | |
| N16 | =N15-N14+1 | |
| N18 | =YEARFRAC(N14,N15+1) | |
| N19 | =YEAR(N14) | |
| N26 | =(1/$L$25) | |
| N29 | =L29 | |
| N30 | =$L$30/12 | |
| N31 | =N158 | |
| N32 | =SUM(N29:N31) | |
| N34 | =IF(N17<=$L$25,-$L29*N26,0) | |
| N37 | =IF(N17<=$L$25,$N30*N26*-1,0) | |
| L25 | =L24*12 | |
| M38 | =EOMONTH(M37,1) | |
| M39 | =EOMONTH(M38,1) | |
| M40 | =EOMONTH(M39,1) | |
| M37 | =N15 | |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| 'Depreciation schedule'!year | ='N:\Portfolio Development\Projects\Budget Runs\June 2002\[Projection through 2003 _ June 02.xls]Fuel Prices'!#REF! | |
This depreciation schedule spans 120 monthly periods. The monthly ending periods are listed in row form from N15:EC15. The matching monthly ending periods are listed in column form from M37:M156.
The formula area ranges from N37:EC156.
What I would like to do is create a formula that I can copy and paste for the entire area from N37:EC156 rather than changing my formula for each row. The formulas I have listed below are working but I would have to copy them and change them over the entire 120 periods, which will work, but will take a long time. I think something along the following guidelines would work quickly where I could just use one formula for the whole area:
• Let’s use row 38 as an example to start:
• If the period in O15 >= the period in M38, then count the columns from $O17:O17, otherwise leave “Blank”. So cell N:38 would be blank;
• If the count of those columns is <=$L$25, then ($O30*O$26);
• If the count of those columns is >$L$25, then 0
The following formulas demonstrate how I am currently copying, pasting and editing for each row rather than using one formula for the entire area.
Cell O38 =IF(COUNT($O17:O17)<=$L$25,($O30*O$26)*-1,0)
Copy Cell O38 and Paste from Cell P38:EC38. The following are the results:
Cell P38 =IF(COUNT($O17:P17)<=$L$25,($O30*P$26)*-1,0)
Cell Q38 =IF(COUNT($O17:Q17)<=$L$25,($O30*Q$26)*-1,0)…
Copy Cell P38 and paste to Cell P39:
Cell P39 =IF(COUNT($O18:P18)<=$L$25,($O31*P$26)*-1,0)
Change the following:
Cell P39 =IF(COUNT($P17:P17)<=$L$25,($P30*P$26)*-1,0)
Copy Cell P39 and Paste from Q39:EC39
Cell Q39 =IF(COUNT($P17:Q17)<=$L$25,($P30*Q$26)*-1,0)
Cell R39 =IF(COUNT($P17:R17)<=$L$25,($P30*R$26)*-1,0)…
