Business Functions Library for Excel

Function Reference Home Date and Time Calculations Introduction Date Information Introduction DYear DMonth DateYMD DateSeqNum IsSeqDate DayName MonthName IsLeap DaysInMonth IsAnniversary WeekNumber WeekNumberToDate MonthStart MonthEnd NumPers MostRecent MostImminent TimeBaseLengths AnnSeq CreateTimeBase Date Difference Introduction DiffY DiffM DiffD DiffP Date Increment Introduction DpY DpM DpD DpP PrevDateSeq NextDateSeq PrevDateList NextDateList NextDateCombine DatePer EDateBF DateSeq DateSeqCombine Time Period Introduction TpY TpM TpD Occurs Special Dates Introduction Business Days Introduction DpYBD DpMBD DpDBD NextBD PrevBD NextDateSeqBD PrevDateSeqBD IsBD NetWorkDaysBF NextDateListBD PrevDateListBD Holidays Introduction Holiday DescribeHol WhatHol IsHol IsWE WesternEaster CreateDate Discrete Amount Projections Introduction Payments Introduction MkPmtsSeq MkPmts MkPmtsGrow MkPmtsFcst MkPmtsSF MkPmtsLim MkPmtsLag MkPmtsLagProf RePhasePmts TotPmts TotPmtsSF SumBetween DoBetween MatchPmts CarryNeg CarryPos MkPmtsX MkPmtsLagX MkPmtsIdx MkPmtsCats MkPmtsGroup Payments Periodic Introduction MkPmtsPer MkPmtsPerGrow MkPmtsPerFcst MkPmtsIdxPer Payments Market Introduction MkPmtsGrowMkt MkPmtsFcstMkt MkPmtsPerGrowMkt MkPmtsPerFcstMkt Distributed Amount Projections Introduction Time Spread Introduction GSpread UniSpread UniSpreadGrow SCurve SCurveGrow DistSpread FStepSpread TStepSpread MkPmtsWeighted SCumCurve Time Spread Periodic Introduction UniSpreadPer SCurvePer SCurveGrowPer UniSpreadGrowPer Time Spread Weighted Introduction WeightSpread FWeightSpread TWeightSpread Time Spread History Introduction UniSpreadH SCurveH WeightSpreadH FWeightSpreadH TWeightSpreadH DistSpreadH GSpreadH UniSpreadAdj SCurveAdj WeightSpreadAdj FWeightSpreadAdj TWeightSpreadAdj DistSpreadAdj GSpreadAdj Constant Rate Projections Introduction Constant Rate Introduction Con ConGrow AnnGrow RePhaseCon AnnRate ConSF ConIdx ConEvent Constant Rate Periodic Introduction ConPer AnnGrowPer ConGrowPer Constant Rate Market Introduction ConGrowMkt ConFcstMkt ConFcstGrowMkt Constant Rate Revenue Introduction ConRevGrow2 ConRev ConRevGrow ConRevFcst Stepped Rate Projections Introduction Stepped Rate From Introduction FStep FStepGrow FStepGrowX FStepThenGrow FStepFcst FStepGrown FStepSF FStepCombine MultiFStep Stepped Rate To Introduction TStep TStepGrow TStepGrowX TStepThenGrow TStepFcst TStepGrown TStepSF TStepCombine MultiTStep Stepped Rate Market Introduction FStepGrowMkt FStepFcstMkt TStepGrowMkt TStepFcstMkt Stepped Rate Revenue Introduction FStepRev TStepRev FStepRevGrow FStepRevFcst TStepRevGrow TStepRevFcst FStepThenGrowRev TStepThenGrowRev Interpolated and Curve-Fitted Projections Introduction Interpolated and Curve-Fitted Rates Introduction FittedRate Ramp Interpolated and Curve-Fitted Levels Introduction InterpedLevel FittedLevel Instantaneous Level Projections Introduction Levels Introduction Bal Level FLevels TLevels FLevelsGrow TLevelsGrow FLevelsFcst TLevelsFcst FLevelsCombine FLevelsThenGrow TLevelsThenGrow Levels Market Introduction FLevelsGrowMkt TLevelsGrowMkt FLevelsFcstMkt TLevelsFcstMkt Growth Introduction Grow GrowFromTo FGrowStep TGrowStep FGrowStepMkt TGrowStepMkt GrowMkt GrowLin Forecast Introduction Fcst FcstGrow FcstCollar SumFcst FFcstStep TFcstStep IdxFromTo Forecast Specific Times Introduction FcstT TFcstT Levels Over Time Projections Introduction Levels Average Introduction ALevel FALevels TALevels FALevelsGrow TALevelsGrow FALevelsFcst TALevelsFcst FALevelsThenGrow TALevelsThenGrow ALevelGrow Levels Average Market Introduction FALevelsGrowMkt TALevelsGrowMkt FALevelsFcstMkt TALevelsFcstMkt Growth Average Introduction AGrow Projections NPV Introduction NPV Constant Rate Introduction PVCon PVAnnGrow PVConGrow PVConQ NPV Stepped Rate Introduction PVTStep PVTStepQ PVTStepIC PVTStepICQ PVFStep PVFStepIC PVFStepQ PVFStepICQ NPV Payments Introduction PVMkPmtsSF PVMkPmts NPV Time Spread Introduction PVGSpread PVUniSpread PVSCurve PVSCurveGrow PVDistSpread PVFStepSpread PVTStepSpread PVMkPmtsWeighted PVSCumCurve Time Value Of Money Analysis Introduction Basic Interest Rate Conversions Introduction SimpleToSimple SimpleToAER SimpleToCont SimpleToDF AERToSimple AERToCont AERToDF ContToSimple ContToAER ContToDF DFToSimple DFToAER DFToCont Advanced Interest Rate Conversions Introduction SimpleToSimpleX SimpleToAERX SimpleToContX SimpleToDFX AERToSimpleX ContToSimpleX DFToSimpleX RateConvert Annuity Introduction FVM PVM PmtM TermM IntRateM PVEGAnn PVEGAnnM PVEGPerAnn PVEGPerp PVEGPerpM PVEGPerPerp SumEGAnn SumEGAnnM SumEGAnnCont SumLGAnn SumLGAnnM SumLGAnnCont Annuity Extended Introduction PVBF PMTBF RateBF US APR Introduction APRToQuoted QuotedToAPR Discounted Cash Flow Introduction DCF One-Off Introduction PVOne FPVOne TPVOne PVOneQ DCF Annual Introduction PVA IRRA PayBackA PayBackDiscA DurationA AvLifeA DCF Even Intervals Introduction PVE IRRE PaybackE PayBackDiscE DurationE AvLifeE DCF Specific Times Introduction PVT IRRT PayBackT PayBackDiscT DurationT AvLifeT PVTIC IRRTIC PVTX IRRTX PVTCX IRRTCX PVTR IRRTR DCF Dispersed Annual Introduction NPVM IRRM CorrectionM CorrectionCont CorrectionDC DurationM AvLifeM EffTimeM Accounting And Tax Introduction Accounting Introduction Depreciate DepreciatedValue DepPoolWIP DepPoolRatio DepPool DepreciatePer DepreciateGrowPer DepreciateFcstPer DepreciateIdxPer LIFOPrice LIFOCalc Tax Introduction FAvRate TAvRate FAvRateTD TAvRateTD Mathematical Calculations Introduction Statistics Introduction LinReg BestPoly SignChanges RSqrdPoly PolyEqn CalcPoly PolyReg Range Operations Introduction Range Lookup Introduction LookupNum LookupN SeekNum PickNum SortPick LookUpNum2D PickMatrix LookupNIndex Seek SeekList SortNum FindIf Range Sum Introduction DoBetweenX SumCat SumCats SumPos SumNeg SumDiv SumBounds SumSubRange SumCombine SelCats Range Reference Introduction Coordinates WorkSheetName RefPlus GetRef Range Calcs Introduction MinBetween MaxBetween MeanNZ WeightAv Range Information Introduction RangeDir Range Manipulation Introduction SortText ApplyToList CombineLists TransposeNum Unique Interpolation Introduction Interp Interpolate InterpDate InterpolateDate Intersection Simulation Modelling Introduction Monte Carlo Introduction Distribution PartialDependency DistCumulative DistUniform DistTriangular DistDoubleTri DistNormal DistLogNormal RandNum NRandom PickRandom DescribeDistType CumProb Real Estate Introduction Rent Introduction RentGrow RentFcst Rent From Introduction FStepRent FStepRentGrow FStepRentFcst FStepRentFcstCollar Rent To Introduction TStepRent TStepRentGrow TStepRentFcst TStepRentGrowCollar TStepRentFcstCollar Rent Reletting Introduction RentFcstR RentGrowR FStepRentGrowR FStepRentFcstR TStepRentGrowR TStepRentFcstR TStepRentGrowSFR FStepRentGrowSFR TStepRentFcstSFR FStepRentFcstSFR UK Valuation Introduction CapValue EqYield CapValueG EqYieldG CapValueGD EqYieldGD NomToTrue TrueToNom YPP YPPDef YPA YPADef CapValueGrowGD CapValueFcstGD EqYieldGrowGD EqYieldFcstGD UK Valuation DCF Introduction CapValueDCF EqYieldDCF CapValueDCFG EqYieldDCFG CapValueDCFGD EqYieldDCFGD NomToTrueDCF TrueToNomDCF YPADefDCF YPPDefDCF TheorYield ImpGrowth ImpDiscRate PVRent TWRR NPV Rent Projections Introduction PVRentGrow PVRentFcst PVTStepRent PVTStepRentGrow PVTStepRentFcst PVTStepRentGrowR PVTStepRentFcstR PVTStepRentGrowIC PVTStepRentGrowRIC PVTStepIdxRentGrowR PVTStepIdxRentFcstR Retail Rent Introduction TStepGTurnRentGrow TStepGTurnRentGrowR Index Rent Introduction IdxRentGrowR IdxRentFcstR IdxRentGrowMktR IdxRentFcstMktR TStepIdxRentGrowR TStepIdxRentFcstR Real Estate Costs Introduction LetCostR LetCostRQ LeasingFeeGrowR FServChargeR ServChargeGrowR ServChargeFcstR FServChargeGrowR FVacServChargeR VacServChargeGrowR VacServChargeFcstR LeasingFeeFcstR Real Estate Costs UK Introduction UKRatesGrow UKRatesFcst UKRatesGrowR UKRatesFcstR UKEmptyRatesFcstR UKEmptyRatesGrowR Lease Information Introduction LeaseEnd ReletOccurs RentDateInfo RentEventOccurs RentEvent LastReview NextReview LastReviewR NextReviewR LeaseInfo Occupancy LeaseDates Banking Introduction Interest Introduction Interest FInterest TInterest TotInterestSF InterestX PVFInterest PVInterestX PVInterest Yield Curve Introduction ZeroCoupon FutureToSpot SpotToSpot Loan Introduction Loan LoanX LoanLin LoanLinX Mortgage MortgageQ LoanPool MortgagePool IdxBondGrow IdxBondFcst Residual Cash Introduction Overdraft CashAcct FlexAcct CurrentAcct STDAcct LoanAcct LoanAcctEM Equity Trading Introduction BetaIndex BetaReturns DescribeReturn TotalReturn Options Introduction OptionBS OptionBinom OptionMonte Petroleum Economics Introduction Petroleum Engineering Introduction STOIIP Decline Curve Introduction Decline DeclineRate DescribeDecline ProdProf AbandonDate StartDecline Text Introduction Numbers To Text Introduction MoneyText Text String Introduction GetToken GetTokens InString ExInt ExFl Miscellaneous Introduction Special Arithmetic Introduction StdEq DiffGaps AddGaps Rounding Introduction SRound SFloor SCeiling RoundWeekDay RoundMonthDay RoundMD RoundDateSeq DateRound DateFloor DateCeiling Disperse RoundList Library Information Introduction BFVer DescribeLoanAcctOutput DescribeCombineSwitch DescribePmtsPerYear DescribeLevelSwitch DescribeBD DescribeDayCount DescribePeriods DescribeLookup DescribeRounding DescribeLoanOutput DescribeShape ArgValues Flow Of Control Introduction Switch SwitchR SwitchT SwitchRT IfElse IfElseR IfElseT IfElseRT Comparisons Introduction CollarDate InBetween Collar Excel Information Introduction 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 Ltd, London, UK Website Design: Webpure