There are different schools of thought when it comes to saying when a formula is too long. I personally like the ‘the shorter the better’ way of thinking. Ideally, in my view, all formulas should be shorter than half the formula bar in Excel.
Every now and then I come across formulae that have been constructed with a complete disrespect for all financial modelling best-practice guidelines and common sense. Sure, one could technically get to the bottom of such an enigma - if you had a couple of weeks of nothing better to do and an obsession with decision tree analysis! These are three examples of what not to do and I don’t think they need any further explanation.
IF-fetishist
=IF(sen5_internal_refinance_option="Y",IF(mon_date1> sen5_refinancing_date,DF9,IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob* IF(AND(mon_date1=EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD(sen5_term_periods,1)>0), +1 +sen5_rate_periodic)^sen5_pmt_freq)* mon_date1Sen5_issue_date)/365,sen5_rate_periodic),0)), IF(AND(sen5_pmt_flag<>0, mon_date1<>sen5_issue_date),sen5_ob *IF(AND(mon_date1= EOMONTH(sen5_issue_date, ROUND(sen5_months_for_first_interest_calculation,0)), MOD( sen5_term_periods,1)<>0),(1+1+ IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic))^ sen5_pmt_freq)* mon_date1sen5_issue_date)365,IF(base_rate_override_option="Y", sen5_rate_periodic_ir_risk,sen5_rate_periodic)),0))
Named-range-bonanza
=IF(EOMONTH(I$2,-Tax.VAT.Reimbursement) <>EOMONTH(Project.Close,0),- SUM(OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement* 30<Model.Start,0,-Tax.VAT.Reimbursement)): OFFSET(I114,0,IF(I2-Tax.VAT.Reimbursement*30< Model.Start,0,IF(AND(I2>=Constr.End, H2<Constr.End),0-Tax.VAT.Reimbursement, -Tax.VAT.Reimbursement)))),0)+IF(AND (EOMONTH(I$2,-Tax.VAT.Reimbursement)>= Project.Close, EOMONTH(I$2,-Tax.VAT.Reimbursement)< EOMONTH(Project.Close,6)),- OFFSET(I114,0,-ROUND((I$2- EOMONTH(Project.Close,0))/(Assumptions!$H$53/CtnP),0))/6,0)
Just plain wrong
=-IF(Debt.Bond1.Flag=1, J$434*IF(AND(J$434=1,K$434=0), I924+SUM(J920:J922), IF(AND(Debt.Bond1.Term*P- (J$11-1)<VLOOKUP($B919,Table.Depreciation,4)*P, Debt.Bond1.Term*P-(J$11-1)>=0),(I924+SUM(J920:J922))/ (Debt.Bond1.Term*P-(J$11-1)+1), (I924+SUM(J920:J922))/( VLOOKUP($B919,Table.Depreciation,4)*P ))),J$368*IF(AND(J$368=1,K$368=0),I924+SUM(J920:J922),IF( AND((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1) <VLOOKUP($B919,Table.Depreciation,4)*P, (Debt.Bank1.Term+Debt.Bank3.Term)*P -(J$11-1)>=0),(I924+SUM(J920:J922))/ ((Debt.Bank1.Term+Debt.Bank3.Term)*P-(J$11-1)+1),(I924 +SUM(J920:J922))/(VLOOKUP($B919,Table.Depreciation,4)*P))))
If you have come across anything worse - let me know!
Post new comment