Business Functions Library for Excel

    1. Introduction
      1. Introduction
      2. DYear
      3. DMonth
      4. DateYMD
      5. DateSeqNum
      6. IsSeqDate
      7. DayName
      8. MonthName
      9. IsLeap
      10. DaysInMonth
      11. IsAnniversary
      12. WeekNumber
      13. WeekNumberToDate
      14. MonthStart
      15. MonthEnd
      16. NumPers
      17. MostRecent
      18. MostImminent
      19. TimeBaseLengths
      20. AnnSeq
      21. CreateTimeBase
      1. Introduction
      2. DiffY
      3. DiffM
      4. DiffD
      5. DiffP
      1. Introduction
      2. DpY
      3. DpM
      4. DpD
      5. DpP
      6. PrevDateSeq
      7. NextDateSeq
      8. PrevDateList
      9. NextDateList
      10. NextDateCombine
      11. DatePer
      12. EDateBF
      13. DateSeq
      14. DateSeqCombine
      1. Introduction
      2. TpY
      3. TpM
      4. TpD
      5. Occurs
    1. Introduction
      1. Introduction
      2. DpYBD
      3. DpMBD
      4. DpDBD
      5. NextBD
      6. PrevBD
      7. NextDateSeqBD
      8. PrevDateSeqBD
      9. IsBD
      10. NetWorkDaysBF
      11. NextDateListBD
      12. PrevDateListBD
      1. Introduction
      2. Holiday
      3. DescribeHol
      4. WhatHol
      5. IsHol
      6. IsWE
      7. WesternEaster
      8. CreateDate
    1. Introduction
      1. Introduction
      2. MkPmtsSeq
      3. MkPmts
      4. MkPmtsGrow
      5. MkPmtsFcst
      6. MkPmtsSF
      7. MkPmtsLim
      8. MkPmtsLag
      9. MkPmtsLagProf
      10. RePhasePmts
      11. TotPmts
      12. TotPmtsSF
      13. SumBetween
      14. DoBetween
      15. MatchPmts
      16. CarryNeg
      17. CarryPos
      18. MkPmtsX
      19. MkPmtsLagX
      20. MkPmtsIdx
      21. MkPmtsCats
      22. MkPmtsGroup
      1. Introduction
      2. MkPmtsPer
      3. MkPmtsPerGrow
      4. MkPmtsPerFcst
      5. MkPmtsIdxPer
      1. Introduction
      2. MkPmtsGrowMkt
      3. MkPmtsFcstMkt
      4. MkPmtsPerGrowMkt
      5. MkPmtsPerFcstMkt
    1. Introduction
      1. Introduction
      2. GSpread
      3. UniSpread
      4. UniSpreadGrow
      5. SCurve
      6. SCurveGrow
      7. DistSpread
      8. FStepSpread
      9. TStepSpread
      10. MkPmtsWeighted
      11. SCumCurve
      1. Introduction
      2. UniSpreadPer
      3. SCurvePer
      4. SCurveGrowPer
      5. UniSpreadGrowPer
      1. Introduction
      2. WeightSpread
      3. FWeightSpread
      4. TWeightSpread
      1. Introduction
      2. UniSpreadH
      3. SCurveH
      4. WeightSpreadH
      5. FWeightSpreadH
      6. TWeightSpreadH
      7. DistSpreadH
      8. GSpreadH
      9. UniSpreadAdj
      10. SCurveAdj
      11. WeightSpreadAdj
      12. FWeightSpreadAdj
      13. TWeightSpreadAdj
      14. DistSpreadAdj
      15. GSpreadAdj
    1. Introduction
      1. Introduction
      2. Con
      3. ConGrow
      4. AnnGrow
      5. RePhaseCon
      6. AnnRate
      7. ConSF
      8. ConIdx
      9. ConEvent
      1. Introduction
      2. ConPer
      3. AnnGrowPer
      4. ConGrowPer
      1. Introduction
      2. ConGrowMkt
      3. ConFcstMkt
      4. ConFcstGrowMkt
      1. Introduction
      2. ConRevGrow2
      3. ConRev
      4. ConRevGrow
      5. ConRevFcst
    1. Introduction
      1. Introduction
      2. FStep
      3. FStepGrow
      4. FStepGrowX
      5. FStepThenGrow
      6. FStepFcst
      7. FStepGrown
      8. FStepSF
      9. FStepCombine
      10. MultiFStep
      1. Introduction
      2. TStep
      3. TStepGrow
      4. TStepGrowX
      5. TStepThenGrow
      6. TStepFcst
      7. TStepGrown
      8. TStepSF
      9. TStepCombine
      10. MultiTStep
      1. Introduction
      2. FStepGrowMkt
      3. FStepFcstMkt
      4. TStepGrowMkt
      5. TStepFcstMkt
      1. Introduction
      2. FStepRev
      3. TStepRev
      4. FStepRevGrow
      5. FStepRevFcst
      6. TStepRevGrow
      7. TStepRevFcst
      8. FStepThenGrowRev
      9. TStepThenGrowRev
    1. Introduction
      1. Introduction
      2. FittedRate
      3. Ramp
      1. Introduction
      2. InterpedLevel
      3. FittedLevel
    1. Introduction
      1. Introduction
      2. Bal
      3. Level
      4. FLevels
      5. TLevels
      6. FLevelsGrow
      7. TLevelsGrow
      8. FLevelsFcst
      9. TLevelsFcst
      10. FLevelsCombine
      11. FLevelsThenGrow
      12. TLevelsThenGrow
      1. Introduction
      2. FLevelsGrowMkt
      3. TLevelsGrowMkt
      4. FLevelsFcstMkt
      5. TLevelsFcstMkt
      1. Introduction
      2. Grow
      3. GrowFromTo
      4. FGrowStep
      5. TGrowStep
      6. FGrowStepMkt
      7. TGrowStepMkt
      8. GrowMkt
      9. GrowLin
      1. Introduction
      2. Fcst
      3. FcstGrow
      4. FcstCollar
      5. SumFcst
      6. FFcstStep
      7. TFcstStep
      8. IdxFromTo
      1. Introduction
      2. FcstT
      3. TFcstT
    1. Introduction
      1. Introduction
      2. ALevel
      3. FALevels
      4. TALevels
      5. FALevelsGrow
      6. TALevelsGrow
      7. FALevelsFcst
      8. TALevelsFcst
      9. FALevelsThenGrow
      10. TALevelsThenGrow
      11. ALevelGrow
      1. Introduction
      2. FALevelsGrowMkt
      3. TALevelsGrowMkt
      4. FALevelsFcstMkt
      5. TALevelsFcstMkt
      1. Introduction
      2. AGrow
    1. Introduction
      1. Introduction
      2. PVCon
      3. PVAnnGrow
      4. PVConGrow
      5. PVConQ
      1. Introduction
      2. PVTStep
      3. PVTStepQ
      4. PVTStepIC
      5. PVTStepICQ
      6. PVFStep
      7. PVFStepIC
      8. PVFStepQ
      9. PVFStepICQ
      1. Introduction
      2. PVMkPmtsSF
      3. PVMkPmts
      1. Introduction
      2. PVGSpread
      3. PVUniSpread
      4. PVSCurve
      5. PVSCurveGrow
      6. PVDistSpread
      7. PVFStepSpread
      8. PVTStepSpread
      9. PVMkPmtsWeighted
      10. PVSCumCurve
    1. Introduction
      1. Introduction
      2. SimpleToSimple
      3. SimpleToAER
      4. SimpleToCont
      5. SimpleToDF
      6. AERToSimple
      7. AERToCont
      8. AERToDF
      9. ContToSimple
      10. ContToAER
      11. ContToDF
      12. DFToSimple
      13. DFToAER
      14. DFToCont
      1. Introduction
      2. SimpleToSimpleX
      3. SimpleToAERX
      4. SimpleToContX
      5. SimpleToDFX
      6. AERToSimpleX
      7. ContToSimpleX
      8. DFToSimpleX
      9. RateConvert
      1. Introduction
      2. FVM
      3. PVM
      4. PmtM
      5. TermM
      6. IntRateM
      7. PVEGAnn
      8. PVEGAnnM
      9. PVEGPerAnn
      10. PVEGPerp
      11. PVEGPerpM
      12. PVEGPerPerp
      13. SumEGAnn
      14. SumEGAnnM
      15. SumEGAnnCont
      16. SumLGAnn
      17. SumLGAnnM
      18. SumLGAnnCont
      1. Introduction
      2. PVBF
      3. PMTBF
      4. RateBF
      1. Introduction
      2. APRToQuoted
      3. QuotedToAPR
    1. Introduction
      1. Introduction
      2. PVOne
      3. FPVOne
      4. TPVOne
      5. PVOneQ
      1. Introduction
      2. PVA
      3. IRRA
      4. PayBackA
      5. PayBackDiscA
      6. DurationA
      7. AvLifeA
      1. Introduction
      2. PVE
      3. IRRE
      4. PaybackE
      5. PayBackDiscE
      6. DurationE
      7. AvLifeE
      1. Introduction
      2. PVT
      3. IRRT
      4. PayBackT
      5. PayBackDiscT
      6. DurationT
      7. AvLifeT
      8. PVTIC
      9. IRRTIC
      10. PVTX
      11. IRRTX
      12. PVTCX
      13. IRRTCX
      14. PVTR
      15. IRRTR
      1. Introduction
      2. NPVM
      3. IRRM
      4. CorrectionM
      5. CorrectionCont
      6. CorrectionDC
      7. DurationM
      8. AvLifeM
      9. EffTimeM
    1. Introduction
      1. Introduction
      2. Depreciate
      3. DepreciatedValue
      4. DepPoolWIP
      5. DepPoolRatio
      6. DepPool
      7. DepreciatePer
      8. DepreciateGrowPer
      9. DepreciateFcstPer
      10. DepreciateIdxPer
      11. LIFOPrice
      12. LIFOCalc
      1. Introduction
      2. FAvRate
      3. TAvRate
      4. FAvRateTD
      5. TAvRateTD
    1. Introduction
      1. Introduction
      2. LinReg
      3. BestPoly
      4. SignChanges
      5. RSqrdPoly
      6. PolyEqn
      7. CalcPoly
      8. PolyReg
    1. Introduction
      1. Introduction
      2. LookupNum
      3. LookupN
      4. SeekNum
      5. PickNum
      6. SortPick
      7. LookUpNum2D
      8. PickMatrix
      9. LookupNIndex
      10. Seek
      11. SeekList
      12. SortNum
      13. FindIf
      1. Introduction
      2. DoBetweenX
      3. SumCat
      4. SumCats
      5. SumPos
      6. SumNeg
      7. SumDiv
      8. SumBounds
      9. SumSubRange
      10. SumCombine
      11. SelCats
      1. Introduction
      2. Coordinates
      3. WorkSheetName
      4. RefPlus
      5. GetRef
      1. Introduction
      2. MinBetween
      3. MaxBetween
      4. MeanNZ
      5. WeightAv
      1. Introduction
      2. RangeDir
      1. Introduction
      2. SortText
      3. ApplyToList
      4. CombineLists
      5. TransposeNum
      6. Unique
      1. Introduction
      2. Interp
      3. Interpolate
      4. InterpDate
      5. InterpolateDate
      6. Intersection
    1. Introduction
      1. Introduction
      2. Distribution
      3. PartialDependency
      4. DistCumulative
      5. DistUniform
      6. DistTriangular
      7. DistDoubleTri
      8. DistNormal
      9. DistLogNormal
      10. RandNum
      11. NRandom
      12. PickRandom
      13. DescribeDistType
      14. CumProb
    1. Introduction
      1. Introduction
      2. RentGrow
      3. RentFcst
      1. Introduction
      2. FStepRent
      3. FStepRentGrow
      4. FStepRentFcst
      5. FStepRentFcstCollar
      1. Introduction
      2. TStepRent
      3. TStepRentGrow
      4. TStepRentFcst
      5. TStepRentGrowCollar
      6. TStepRentFcstCollar
      1. Introduction
      2. RentFcstR
      3. RentGrowR
      4. FStepRentGrowR
      5. FStepRentFcstR
      6. TStepRentGrowR
      7. TStepRentFcstR
      8. TStepRentGrowSFR
      9. FStepRentGrowSFR
      10. TStepRentFcstSFR
      11. FStepRentFcstSFR
      1. Introduction
      2. CapValue
      3. EqYield
      4. CapValueG
      5. EqYieldG
      6. CapValueGD
      7. EqYieldGD
      8. NomToTrue
      9. TrueToNom
      10. YPP
      11. YPPDef
      12. YPA
      13. YPADef
      14. CapValueGrowGD
      15. CapValueFcstGD
      16. EqYieldGrowGD
      17. EqYieldFcstGD
      1. Introduction
      2. CapValueDCF
      3. EqYieldDCF
      4. CapValueDCFG
      5. EqYieldDCFG
      6. CapValueDCFGD
      7. EqYieldDCFGD
      8. NomToTrueDCF
      9. TrueToNomDCF
      10. YPADefDCF
      11. YPPDefDCF
      12. TheorYield
      13. ImpGrowth
      14. ImpDiscRate
      15. PVRent
      16. TWRR
      1. Introduction
      2. PVRentGrow
      3. PVRentFcst
      4. PVTStepRent
      5. PVTStepRentGrow
      6. PVTStepRentFcst
      7. PVTStepRentGrowR
      8. PVTStepRentFcstR
      9. PVTStepRentGrowIC
      10. PVTStepRentGrowRIC
      11. PVTStepIdxRentGrowR
      12. PVTStepIdxRentFcstR
      1. Introduction
      2. TStepGTurnRentGrow
      3. TStepGTurnRentGrowR
      1. Introduction
      2. IdxRentGrowR
      3. IdxRentFcstR
      4. IdxRentGrowMktR
      5. IdxRentFcstMktR
      6. TStepIdxRentGrowR
      7. TStepIdxRentFcstR
      1. Introduction
      2. LetCostR
      3. LetCostRQ
      4. LeasingFeeGrowR
      5. FServChargeR
      6. ServChargeGrowR
      7. ServChargeFcstR
      8. FServChargeGrowR
      9. FVacServChargeR
      10. VacServChargeGrowR
      11. VacServChargeFcstR
      12. LeasingFeeFcstR
      1. Introduction
      2. UKRatesGrow
      3. UKRatesFcst
      4. UKRatesGrowR
      5. UKRatesFcstR
      6. UKEmptyRatesFcstR
      7. UKEmptyRatesGrowR
      1. Introduction
      2. LeaseEnd
      3. ReletOccurs
      4. RentDateInfo
      5. RentEventOccurs
      6. RentEvent
      7. LastReview
      8. NextReview
      9. LastReviewR
      10. NextReviewR
      11. LeaseInfo
      12. Occupancy
      13. LeaseDates
    1. Introduction
      1. Introduction
      2. Interest
      3. FInterest
      4. TInterest
      5. TotInterestSF
      6. InterestX
      7. PVFInterest
      8. PVInterestX
      9. PVInterest
      1. Introduction
      2. ZeroCoupon
      3. FutureToSpot
      4. SpotToSpot
      1. Introduction
      2. Loan
      3. LoanX
      4. LoanLin
      5. LoanLinX
      6. Mortgage
      7. MortgageQ
      8. LoanPool
      9. MortgagePool
      10. IdxBondGrow
      11. IdxBondFcst
      1. Introduction
      2. Overdraft
      3. CashAcct
      4. FlexAcct
      5. CurrentAcct
      6. STDAcct
      7. LoanAcct
      8. LoanAcctEM
      1. Introduction
      2. BetaIndex
      3. BetaReturns
      4. DescribeReturn
      5. TotalReturn
      1. Introduction
      2. OptionBS
      3. OptionBinom
      4. OptionMonte
    1. Introduction
      1. Introduction
      2. STOIIP
      1. Introduction
      2. Decline
      3. DeclineRate
      4. DescribeDecline
      5. ProdProf
      6. AbandonDate
      7. StartDecline
    1. Introduction
      1. Introduction
      2. MoneyText
      1. Introduction
      2. GetToken
      3. GetTokens
      4. InString
      5. ExInt
      6. ExFl
      1. Introduction
      2. RegSMatch
      3. RegMatchWhole
      4. RegNMatches
      5. RegReplace
      6. RegSMatches
      7. RegSplit
    1. Introduction
      1. Introduction
      2. StdEq
      3. DiffGaps
      4. AddGaps
      1. Introduction
      2. SRound
      3. SFloor
      4. SCeiling
      5. RoundWeekDay
      6. RoundMonthDay
      7. RoundMD
      8. RoundDateSeq
      9. DateRound
      10. DateFloor
      11. DateCeiling
      12. Disperse
      13. RoundList
      1. Introduction
      2. BFVer
      3. DescribeLoanAcctOutput
      4. DescribeCombineSwitch
      5. DescribePmtsPerYear
      6. DescribeLevelSwitch
      7. DescribeBD
      8. DescribeDayCount
      9. DescribePeriods
      10. DescribeLookup
      11. DescribeRounding
      12. DescribeLoanOutput
      13. DescribeShape
      14. ArgValues
      1. Introduction
      2. Switch
      3. SwitchR
      4. SwitchT
      5. SwitchRT
      6. IfElse
      7. IfElseR
      8. IfElseT
      9. IfElseRT
      1. Introduction
      2. CollarDate
      3. InBetween
      4. Collar
      1. Introduction
      2. Ev
Time Spread History family
Time Spread functions that take into account an existing payment history and adjust the forward forecast accordingly
Description:
Introduction: The Time Spread History family is an extension of the Time Spread family that contains functions that can be adjusted for historical payments or payments made "to date", a problem often found in budgeting. The aim of these functions is to make the overall amount disbursed equal to the overall Total, adjusting the future rate of disbursement accordingly.

There are two ways in which the payments to date are taken into account:
  • One way is to explicitly specify a payments history using the variables DatesHistory and PmtsHistory. The remainder of the Total, after taking into account the historical payments, is spread according the different kind of spread mechanism in the function. The way these functions work is that firstly the historical payments are calculated to see if any historical payments occur in the timeperiod. Then the Total is reduced by the total amount of historical payments made to date, or payments yet to be made. Then the function tries to spread the remaining total in its usual way, either uniformly in the case of UniSpread,or in an S-Curve as in SCurve, etc. It will probably do this calculation internally more than once and adjust it, to make sure that no more or less than the Total is dispersed. To make it absolutely clear, the function will always disperse exactly the Total over the period of the projection, no more and no less.
  • A second and more direct way is to specify the overall amount incurred prior to Time in a single ˙ActualsToDate˙ variable, for example in the UniSpreadAdj function. Here the Total is spread over the appropriate length of time after allowing for the ActualsToDate. This is not as trivial a calculation as first appears, because for functions like SCurveAdj the remainder of the curve has to honour the original shape before the actuals were incurred. Indeed, the function arrives at the forecast by pro-rata-ing the original forecast so that the total forecast by the function, plus the ActualsToDate, always comes to the Total. See the example of the SCurveAdj function for a demonstration of this principle. Again, the function will always disperse exactly the Total over the period of the projection, no more and no less.

Note: These functions will disperse the Total, no less and no more, regardless of the amount of historical payments specified.

These functions are very good for budgeting, where you always have a projection for the future and are constantly updating the past data. Without this family of functions it is a pain to keep changing your forecast to reflect actuals, whereas these functions do that job for you.
Functions in the Time Spread History family (14)
  • UniSpreadH UniSpread that uses historical data
  • SCurveH SCurve that uses historical data
  • WeightSpreadH WeightSpread that uses historical data
  • FWeightSpreadH FWeightSpread that uses historical data
  • TWeightSpreadH WeightSpread that uses historical data
  • DistSpreadH DistSpread that uses historical data
  • GSpreadH GSpread that uses historical data
  • UniSpreadAdj UniSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • SCurveAdj SCurve that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • WeightSpreadAdj WeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • FWeightSpreadAdj FWeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • TWeightSpreadAdj TWeightSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • DistSpreadAdj DistSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.
  • GSpreadAdj GSpread that uses an ActualsToDate figure to continually reforecast so that the overall budget stays at Total.

Business Functions phone: +44 (0)20 8144 9374 (London UK)
Website Design: Webpure