I need to calculate a Present Value (PV) in a table for rent. I can not (after many, many tries) come up with the formcalc script language that mirrors the Excel formula my boss has used in a spreadsheet.
I know the 'formula' is PV(n1, n2, n3), but that is apparently NOT what needs to be written as the script. Do I use ".rawValue" in writing this out? Do I use the ctrl+applicable cell in choosing a segment?
My first example of needing to calculate this is having a total amount for a year's rent, a discount rate of [user entered, example would be 5%], and a period of the term year minus 1 (so year 1 would be 0 or not used; year 2 would be 1, year 3 would be 2). This is what the Excel formula relates to, although it uses a negative (minus) in front of the year's rent amount.
Writing PV(yramount, discountrate, yr-1) doesn't do anything to create the answer.
Do I write ... $.rawValue = PV(yramount.rawValue, discountrate.rawValue, yr-1.rawValue) or ....
I can't figure it out (and I've tried this formula every way I can imagine and I'm getting nowhere).
I am also going to be calculating the Present Value of Total Lease Obligations each year (which is another oddity of discountrate, yr-1, negative TotalLeaseobligation).
Once I get 'row 1' calculating, the following rows will be added based on the number of term years, and the calculating will include percentage increases over the prior year along the way. I can't wait 😮
If someone could please help me crack the "actually USING in a form the formula for Present Value)", I will be so very grateful. It is decidedly beyond my sub-novice attempts.
Oh, my, thank you so much! It is truly baffling to me. I’ve attached my efforts for what it’s worth. I try to explain more below.
I was basing my ‘assumption’ on this ‘formcalc information’ – but obviously I couldn’t do anything with it as it is too cryptic for a beginner.
Here is the Excel formula used in the current spreadsheet:
Where B22 is the Discount rate (e.g. 5%)
B26 is a number representing the year number of the prior year in the Term (e.g., the first year pulls a blank cell, the 2nd year pulls “1”, the 3rd year pulls “2”)
C28 is the Base Rent amount for the year being calculated, but shown as a negative (?) [That number is calculated as a $/square foot * number of square feet]
In my LCD form, I’m calculating in a table in which there is a header row, a footer row, currently 2 body rows, and I’m trying to get all the values to calculate based on information “provided above” in the form. The first challenge is the first row and I’ve gotten everything to ‘work’ except the “Total Base Rent(PV)” and the “Total Lease Obligation(PV)” the 3rd PV value is an arithmetic calculation between these results.
Current Row 1 formulas:
Base Rent: $.rawValue = NF3BaseRentPersf.rawValue * NF1SquareFootage.rawValue
TI Costs: $.rawValue = NF14AnnDebtServTotal.rawValue
Furniture Fixtures Equipment: $.rawValue = NF14FFETotal.rawValue
Move Costs: $.rawValue = NF14MoveCostsTotal.rawValue
Total Lease Obligations: $.rawValue = Cell2.rawValue + Cell3.rawValue + Cell4.rawValue + Cell5.rawValue
Common Area Costs: $.rawValue = NF4OpExPersf.rawValue * NF1SquareFootage.rawValue
Utilities: $.rawValue = NF5UtilPersf.rawValue * NF1SquareFootage.rawValue
Other Expenses: $.rawValue = NF6OtherPersf.rawValue * NF1SquareFootage.rawValue
Total Operating Expenses: $.rawValue = Cell7.rawValue + Cell8.rawValue + Cell9.rawValue
TOTAL Estimated Costs: $.rawValue = Cell6.rawValue + Cell10.rawValue
Total Base Rent (PV): ______________________?
Total TI & Other Costs (PV): $.rawValue = Cell14.rawValue - Cell12.rawValue
Total Lease Obligation (PV): _______________________ ? (THIS -> doesn't work: $.rawValue = PV(NF15DiscountRate.rawValue,, -Cell6.rawValue)
I'm sorry for being so naive with all this. We are gobsmacked (in a great way) with the possibilities of LCD-- IF I can just get the hang of this stuff.
Here's the hierarchy for the table:
and these are related hierarchy references (you can tell I haven't quite gotten the naming of objects down very well).
AND my NEXT challenges: I am trying to make this a ?repeating row table? And will attempt to add an 'add row' button that will allow the addition of a row reflecting the number of years in the proposed term (from 1-10, usually). So I need the amount to sum in the footer as well as the couple of sums within each row. Starting with the 2nd year, the Base Rent and Operating Expenses amounts will be a factor of the prior year's amount * the applicable Escalation % (Base Rent Escalation or Estimated Annual Expenses Escalation) -- which is why I figured I had to go on and include the 2nd row to put those formulas therein. I am ASSUMING that when I enerate subsequent rows with the "Add Row" button (crossing fingers!) that the formulas will duplicate and the correct amounts will be reflected, including the right totals -- although for a 1-year term I'll need to figure out how to 'delete row' the 2nd row so the totals aren't ka-flooey.
I am extremely grateful for your help in any way you can share. This is a lot of fun -- but I just don't speak the LCD language yet. Thus I flounder.
p.s. I am not sure how to provide a copy of my actual form-in-progress. Much of what I'd written in reply was stripped out so I've come back in to edit and add what I'm able.Thanks again!
Excel PV function accepts a couple more values than those that are accepted by LiveCycle PV function
The PV function in Excel is based on the TVM equation that may be written as the discounted sum of present value of terminal value, the present value of periodic payment and the undiscounted initial investment. With such an equation, one can solve for different aspects of TVM equation such as
This the first time I have come across LiveCycle so I looked up the PV function and found the documentation of Adobe site where it states that the PV function in LiveCycle finds the present value of periodic payments
This is in contrast to the Excel PV function which itself has a lot of flaws and limitations that are addressed by 3rd party add-in programs such as TADXL
Now back to the PV function in LiveCycle which accepts only three values
The documentation makes it clear that the function will report an error if one were to pass negative values for n1 and n3
Say you have three values
Your base rent as n1 (must be positive) in LiveCycle
Your monthly interest rate n1 which is found by dividing the annual rate by 12
And number of months n3
n1 = 1000
n2 = 0.05/12
n3 = 5
thus LiveCycle PV function as follows
=PV(n1, n2, n3)
would be equivalent to Excel PV function as follows
=PV(n2, n3, -n1, 0, 0)
But you see this result would mean that the first payment is discounted at time period 1
If you wanted the first payment discounted at time period 0, then in Excel PV function you would use a value of 1 for argument called TYPE
Sorry there is no argument called TYPE in LiveCycle thus you would have to amend your formula in LiveCycle to bring back the present value to time period 0 by multiplying the answer of LiveCycle PV function by an extra interest factor
Thus this in LiveCycle would give the present value of annuity due where the first payment is left undiscounted
=PV(n1,n2,n3) * (1+n2)
The (1+n2) is the interest factor that brings back the present value of an ordinary annuity to time period 0
Now if you wanted the same functionality of Excel PV function in LiveCycle then you would have to write your own code
I will give you the TVM equation and you would go ahead and convert it to LiveCycle user defined functions the same as Excel
The TVM equation is
FV * (1+r)^-N + PMT* (1+r*TYPE)* [1-(1+r)^-N]/r + PV = 0
But then this is the most trivial form of TVM function thus makes the Excel PV function useless. You would need to extend this equation to cater for cases where periodic payments may grow, shrink, increase and decrease. This latter aspect is reffered to as a GRADIENT.
The only possible solution for Excel in extending the functionality of TVM functions as explained in the last paragraph is addressed in Excel add-in called TADXL
Good Luck with your efforts in getting LiveCycle PV function to solve your immediate concerns
Message was edited by: FinancialEngineer
FinEng, Thank you SO MUCH!
I'm going to take the time to really absorb this and try to make it work. I really appreciate your clear explanation of it all -- and wish I were as smart as you! 🙂
I will wave if/when I figure out how to make this work in my form, and share what finally works.