Payback formula

[Hatle Mehta]

Hi,

Sorry - press sent accidently, on my previous query.

I need a formula showing when an amount is paid back over
a certain period.

For example:

Col Revenue

A - Jan 5000
B - Feb 10000
C - March 3000

Tot 18000

An asset is bought for £6k. I want excel to return a value
saying the asset can be offest against the revenue in the
month of Feb and also the amount of days in Feb.

Please advise.

Thanks

Hatle

 

[Myrna Larson]

Here's a VBA function that will return the date on which the amount will be
paid off. After copying the code to a general module in your workbook, you can
call the function from a worksheet cell with a formula like this:

=PayOff(C5,A2:L2)

where the amount (6000 in your example) is in C5, and the series of monthly
revenues is in A2:L2. The latter range must consist of a single row (e.g.
A2:L2) or single column (e.g. A2:A13), not a rectangular range like A2:L13. It
assumes these revenues are for the current year, with the 1st cell the revenue
for Jan, the 2nd for Feb, etc.

It returns "Insufficient revenues!" if the amount exceeds the total revenues.

Option Explicit

Function PayOff(Amount As Double, CashFlows As Range) As Variant
Dim FraxOfRevNeeded As Double
Dim Mo As Long
Dim NumDaysNeeded As Long
Dim RevPriorMonths As Double
Dim RevThisMonth As Double

With CashFlows
If .Rows.Count > 1 And .Columns.Count > 1 Then
'must be a vector
PayOff = CVErr(xlErrRef)
Exit Function
End If

RevPriorMonths = 0
For Mo = 1 To .Cells.Count
RevThisMonth = .Cells(Mo).Value
If (RevPriorMonths + RevThisMonth) >= Amount Then
FraxOfRevNeeded = (Amount - RevPriorMonths) / RevThisMonth
NumDaysNeeded = Int(FraxOfRevNeeded * _
Day(DateSerial(Year(Date), Mo + 1, 0)) + 0.5)
PayOff = DateSerial(Year(Date), Mo, NumDaysNeeded)
Exit Function
Else
RevPriorMonths = RevPriorMonths + RevThisMonth
End If
Next Mo
PayOff = "Insufficient revenues!"
End With
End Function

 

[Harlan Grove]

Here's a VBA function that will return the date on which the amount will
be paid off. After copying the code to a general module in your workbook,
you can call the function from a worksheet cell with a formula like this:

=PayOff(C5,A2:L2)

....

If the cashflows were in a 2-column by arbitrarily many row range named MCF
with months in the first column and monthly revenues in the second, and the
initial payout in a cell named POUT, you could use the array formula

=TREND(DATEVALUE(OFFSET(MCF,MATCH(TRUE,
MMULT(--(ROW(MCF)>=TRANSPOSE(ROW(MCF))),
INDEX(MCF,0,2))>POUT,0)-1,0,2,1)&"-1"),
MMULT({1,0;1,1},OFFSET(MCF,MATCH(TRUE,
MMULT(--(ROW(MCF)>=TRANSPOSE(ROW(MCF))),
INDEX(MCF,0,2))>POUT,0)-2,1,2,1)),POUT)

 

[Guest]

Hi,


Thanks for that. I don't understand the 'Option Explicit'.
Do i need to copy those formulas in an excel workbook
before I construct the '=payoff' formula.

If I do =payoff(c5, A2:A5) it returns 'NAME'

Please advise.

thanks

Hatle