This is a discussion on PValue(cfs, per, r), function that returns present value. No excel functions allowed within the Excel Questions forums, part of the Question Forums category; Hi Everyone - can any of you help me with this question in my assignment? I'd be very grateful for ...
Hi Everyone - can any of you help me with this question in my assignment?
I'd be very grateful for any assistance!
. Write a function named PValue(cfs, per, r) that returns the present value of a set of cash flows contained in the range cfs (cfs can be either a row or a column.). The range per contains the periods in which each cash flow occurs (e.g. the first cash flow in cfs occurs in period 2, the second cash flow is in period 5). The discount rate is r per period. Do not use any Excel functions within PValue().
The present value of a cash flow is calculated with the formula:
PV = FV * (1 + i)^-n
where FV is the future value, i is the discount rate and n is the number of periods.
With this data in A1:C4:
cfs per r 5000 2 5% 10000 5 15000 7
and this function:
the formula:Code:Function PValue(cfs As Range, per As Range, r As Range) Dim i As Long For i = 1 To cfs.Count PValue = PValue + (cfs.Cells(i).Value * ((1 + r.Value) ^ -per.Cells(i).Value)) Next i End Function
Microsoft MVP - Excel