Results 1 to 2 of 2

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

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

  1. #1
    New Member
    Join Date
    Aug 2009
    Location
    New Zealand
    Posts
    13

    Default 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. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    68,998

    Default 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:

    cfsperr
    500025%
    100005
    150007


    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.
    Microsoft MVP - Excel

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com