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
Interpolation family
Interpolation
Description:
Introduction: This family just deals with interpolation, where the number you are looking up may fall inbetween two values, and you want to in a sense get an average. Technically, of course, it is about putting the ranges you are looking up against on a graph (x=input, y=output) and returning the appropriate y value for the input x. This procedure is very distinct from curve-fitting, which you will see elsewhere in the library, because whereas a curve-fit fits a mathematical equation to some data where there is no guarantee that the equation goes through all the points, with interpolation the resultant curve must go through all the data points. In this sense Interpolation is perhaps more useful to financial analysts as it has less uncertainty involved, and who are not so concerned with the mathematical equation of the data, even if one actually exists.

The are two kinds of function here, one that deals with plain numbers, and one that deals with a special kind of number that optionally uses extra inputs, dates. Within the two kinds of number and date functions, there are two ?flavours?. There is a short-form function that tries to keep the number of inputs to a minimum, and an ?all-singing? function that has the full range of inputs.

The simple number interpolations are Interp (the short-form) and Interpolate (the all-singer).

The date interpolation functions return a number given an InputDateValue, so this is where you might want to read an index or a price from a range governed by dates.

Types of Interpolation
Many of us think of interpolation as being just an exercise in linear or straight-line interpolation, or, when the input value is outside the data, extrapolation. In truth, very often when you think something cleverer than plain linear interpolation ought to work well, linear interpolations is actually the best thing to use. It has the benefit that people understand it and it is readily auditable and there is sometimes a resistance to doing something smarter.

The default for our interpolation functions is always linear interpolation, but we do aim to cover the standard forms of mathematical interpolation in its wider sense, and have put in one method, exponential, that we ?borrowed? from techniques used in the petroleum industry, would you believe.

The basic curve types available are cosine and cubic. Cosine is very good for producing a flowing, continuous curve, as is cubic, and they do not require any other inputs other than you specify the correct InterpType. Hermite is similar to Cubic, but you can optionally specify two extra variables that can further determine its shape, Tension and Bias.

Exponential interpolation as implemented here has been specially developed by the Business Functions programmers, although the idea has been borrowed from the logic used in oil reservoirs where there is a constant rate of production decline. This logic has a particular applicability and theoretical relationship with any circumstance where there is a constant and continuous rate of decline or indeed growth, so inflation and continuous interest also fit this model. You only need to set InterpType (to 4) to model exponential interpolation, but you can also set a ?hyperbolic? exponent in the Tension variable, which in the special case of 1 becomes harmonic (growth or decline). Note that what exponential interpolation can?t do is guarantee a smooth curve, and if your data is more than 2 points and is not exponential in the underlying sense, there will be sharp changes of gradient (discontinuities), at the data points themselves.
Functions in the Interpolation family (5)

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