Thanks:  0
Likes:  0

# Thread: PValue(cfs, per, r), function that returns present value. No excel functions allowed

1. ## PValue(cfs, per, r), function that returns present value. No excel functions allowed

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().

2. ## Re: PValue(cfs, per, r), function that returns present value. No excel functions allowed

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:

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```
the formula:

=PValue(A2:A4,B2:B4,C2)

returns 23,030.63.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•