Expand my Community achievements bar.

Dive into Adobe Summit 2024! Explore curated list of AEM sessions & labs, register, connect with experts, ask questions, engage, and share insights. Don't miss the excitement.

FormCalc script language for calculating PV

Avatar

Level 2

Greetings,

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

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.

Thank you!

Shu

4 Replies

Avatar

Level 9

Hi,

There is no such function called Present Value in FormCalc or in JavaScript. Yo need to use $rawValue of the textfields that are placed inside the row. If you can give me the complete mathematical formula I can help you in writing the script.

Thanks,

Bibhu.

Avatar

Level 2

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.

P-1.jpgP-2.jpgp-3.jpg

 

Here is the Excel formula used in the current spreadsheet:

= PV($B$22,B$26,,-C28

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.

p-4.jpg

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:

p-5.jpg

and these are related hierarchy references (you can tell I haven't quite gotten the naming of objects down very well).

p-6.jpgp-7.jpg

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.

Appreciatively,

Shu

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!

Avatar

Former Community Member

Hello Shu

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

  1. present value of a lump sum
  2. future value of a lump sum
  3. present value of periodic payments
  4. future value of periodic payments
  5. the number of periods with single sum investment
  6. the number of periods with mutiple return investments
  7. the interest rate for single sum and multiple return investments

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

  1. Periodic payment n1
  2. Interest rate per period n2
  3. Number of periods n3

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

Avatar

Level 2

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.

Appreciatively,

Shu