topic Re: FormCalc script language for calculating PV in Adobe LiveCycle Discussions
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114109#M44478
<HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>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><P></P><P><span class="lia-inline-image-display-wrapper" image-alt="P-1.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3333i66E6F97C282A7748/image-size/large?v=v2&px=999" role="button" title="P-1.jpg" alt="P-1.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="P-2.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3334iD6EDC5A80CAF0786/image-size/large?v=v2&px=999" role="button" title="P-2.jpg" alt="P-2.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="p-3.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3335i2069D26E45AAC6AD/image-size/large?v=v2&px=999" role="button" title="p-3.jpg" alt="p-3.jpg" /></span></P><P> </P><P></P><P>Here is the Excel formula used in the current spreadsheet:</P><P></P><P>= PV($B$22,B$26,,-C28</P><P></P><P>Where B22 is the Discount rate (e.g. 5%)</P><P>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”)</P><P>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]</P><P></P><P></P><P>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)<STRONG>” and the “Total Lease Obligation(PV)</STRONG>” the 3rd PV value is an arithmetic calculation between these results.</P><P></P><P><span class="lia-inline-image-display-wrapper" image-alt="p-4.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3336i8158B0C23E0F0765/image-size/large?v=v2&px=999" role="button" title="p-4.jpg" alt="p-4.jpg" /></span></P><P></P><P></P><P>Current Row 1 formulas:</P><P>Base Rent: $.rawValue = NF3BaseRentPersf.rawValue * NF1SquareFootage.rawValue</P><P>TI Costs: $.rawValue = NF14AnnDebtServTotal.rawValue</P><P>Furniture Fixtures Equipment: $.rawValue = NF14FFETotal.rawValue</P><P>Move Costs: $.rawValue = NF14MoveCostsTotal.rawValue</P><P>Total Lease Obligations: $.rawValue = Cell2.rawValue + Cell3.rawValue + Cell4.rawValue + Cell5.rawValue</P><P>Common Area Costs: $.rawValue = NF4OpExPersf.rawValue * NF1SquareFootage.rawValue</P><P>Utilities: $.rawValue = NF5UtilPersf.rawValue * NF1SquareFootage.rawValue</P><P>Other Expenses: $.rawValue = NF6OtherPersf.rawValue * NF1SquareFootage.rawValue</P><P>Total Operating Expenses: $.rawValue = Cell7.rawValue + Cell8.rawValue + Cell9.rawValue</P><P>TOTAL Estimated Costs: $.rawValue = Cell6.rawValue + Cell10.rawValue</P><P>Total Base Rent (PV): ______________________?</P><P>Total TI & Other Costs (PV): $.rawValue = Cell14.rawValue - Cell12.rawValue</P><P>Total Lease Obligation (PV): _______________________ ? (THIS -> doesn't work: $.rawValue = PV(NF15DiscountRate.rawValue,, -Cell6.rawValue)</P><P></P><P>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. </P><P></P><P>Here's the hierarchy for the table:</P><P><span class="lia-inline-image-display-wrapper" image-alt="p-5.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3337i7289D52AF60268C5/image-size/large?v=v2&px=999" role="button" title="p-5.jpg" alt="p-5.jpg" /></span></P><P></P><P>and these are related hierarchy references (you can tell I haven't quite gotten the naming of objects down very well).</P><P><span class="lia-inline-image-display-wrapper" image-alt="p-6.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3338i13ABD90957113387/image-size/large?v=v2&px=999" role="button" title="p-6.jpg" alt="p-6.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="p-7.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3339i88C1BAD0C600145C/image-size/large?v=v2&px=999" role="button" title="p-7.jpg" alt="p-7.jpg" /></span></P><P></P><P>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.</P><P></P><P>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><P></P><P>Appreciatively,</P><P>Shu</P><P></P><P>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!</P></BODY></HTML>Wed, 27 Mar 2013 13:23:48 GMTLEShumaker2013-03-27T13:23:48ZFormCalc script language for calculating PV
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114107#M44476
<HTML><HEAD></HEAD><BODY><P>Greetings,</P><P></P><P>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.</P><P></P><P>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?</P><P></P><P>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.</P><P></P><P>Writing PV(yramount, discountrate, yr-1) doesn't do anything to create the answer.</P><P></P><P>Do I write ... $.rawValue = PV(yramount.rawValue, discountrate.rawValue, yr-1.rawValue) or .... </P><P>I can't figure it out (and I've tried this formula every way I can imagine and I'm getting nowhere).</P><P></P><P>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).</P><P></P><P>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 <span class="lia-unicode-emoji" title=":face_with_open_mouth:">😮</span></P><P></P><P>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.</P><P></P><P>Thank you!</P><P>Shu</P></BODY></HTML>Tue, 26 Mar 2013 21:19:33 GMThttps://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114107#M44476LEShumaker2013-03-26T21:19:33ZRe: FormCalc script language for calculating PV
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114108#M44477
<HTML><HEAD></HEAD><BODY><P>Hi,</P><P></P><P>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.</P><P></P><P>Thanks,</P><P>Bibhu.</P></BODY></HTML>Wed, 27 Mar 2013 08:47:16 GMThttps://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114108#M44477Bibhu_Bikash_Na2013-03-27T08:47:16ZRe: FormCalc script language for calculating PV
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114109#M44478
<HTML><HEAD></HEAD><BODY><P>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.</P><P></P><P>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><P></P><P><span class="lia-inline-image-display-wrapper" image-alt="P-1.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3333i66E6F97C282A7748/image-size/large?v=v2&px=999" role="button" title="P-1.jpg" alt="P-1.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="P-2.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3334iD6EDC5A80CAF0786/image-size/large?v=v2&px=999" role="button" title="P-2.jpg" alt="P-2.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="p-3.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3335i2069D26E45AAC6AD/image-size/large?v=v2&px=999" role="button" title="p-3.jpg" alt="p-3.jpg" /></span></P><P> </P><P></P><P>Here is the Excel formula used in the current spreadsheet:</P><P></P><P>= PV($B$22,B$26,,-C28</P><P></P><P>Where B22 is the Discount rate (e.g. 5%)</P><P>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”)</P><P>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]</P><P></P><P></P><P>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)<STRONG>” and the “Total Lease Obligation(PV)</STRONG>” the 3rd PV value is an arithmetic calculation between these results.</P><P></P><P><span class="lia-inline-image-display-wrapper" image-alt="p-4.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3336i8158B0C23E0F0765/image-size/large?v=v2&px=999" role="button" title="p-4.jpg" alt="p-4.jpg" /></span></P><P></P><P></P><P>Current Row 1 formulas:</P><P>Base Rent: $.rawValue = NF3BaseRentPersf.rawValue * NF1SquareFootage.rawValue</P><P>TI Costs: $.rawValue = NF14AnnDebtServTotal.rawValue</P><P>Furniture Fixtures Equipment: $.rawValue = NF14FFETotal.rawValue</P><P>Move Costs: $.rawValue = NF14MoveCostsTotal.rawValue</P><P>Total Lease Obligations: $.rawValue = Cell2.rawValue + Cell3.rawValue + Cell4.rawValue + Cell5.rawValue</P><P>Common Area Costs: $.rawValue = NF4OpExPersf.rawValue * NF1SquareFootage.rawValue</P><P>Utilities: $.rawValue = NF5UtilPersf.rawValue * NF1SquareFootage.rawValue</P><P>Other Expenses: $.rawValue = NF6OtherPersf.rawValue * NF1SquareFootage.rawValue</P><P>Total Operating Expenses: $.rawValue = Cell7.rawValue + Cell8.rawValue + Cell9.rawValue</P><P>TOTAL Estimated Costs: $.rawValue = Cell6.rawValue + Cell10.rawValue</P><P>Total Base Rent (PV): ______________________?</P><P>Total TI & Other Costs (PV): $.rawValue = Cell14.rawValue - Cell12.rawValue</P><P>Total Lease Obligation (PV): _______________________ ? (THIS -> doesn't work: $.rawValue = PV(NF15DiscountRate.rawValue,, -Cell6.rawValue)</P><P></P><P>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. </P><P></P><P>Here's the hierarchy for the table:</P><P><span class="lia-inline-image-display-wrapper" image-alt="p-5.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3337i7289D52AF60268C5/image-size/large?v=v2&px=999" role="button" title="p-5.jpg" alt="p-5.jpg" /></span></P><P></P><P>and these are related hierarchy references (you can tell I haven't quite gotten the naming of objects down very well).</P><P><span class="lia-inline-image-display-wrapper" image-alt="p-6.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3338i13ABD90957113387/image-size/large?v=v2&px=999" role="button" title="p-6.jpg" alt="p-6.jpg" /></span><span class="lia-inline-image-display-wrapper" image-alt="p-7.jpg"><img src="https://experienceleaguecommunities.adobe.com/t5/image/serverpage/image-id/3339i88C1BAD0C600145C/image-size/large?v=v2&px=999" role="button" title="p-7.jpg" alt="p-7.jpg" /></span></P><P></P><P>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.</P><P></P><P>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><P></P><P>Appreciatively,</P><P>Shu</P><P></P><P>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!</P></BODY></HTML>Wed, 27 Mar 2013 13:23:48 GMThttps://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114109#M44478LEShumaker2013-03-27T13:23:48ZRe: FormCalc script language for calculating PV
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114110#M44479
<HTML><HEAD></HEAD><BODY><P>Hello Shu</P><P></P><P>Excel PV function accepts a couple more values than those that are accepted by LiveCycle PV function</P><P></P><P>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</P><P></P><OL><LI style="text-align: start;">present value of a lump sum</LI><LI style="text-align: start;">future value of a lump sum</LI><LI style="text-align: start;">present value of periodic payments</LI><LI style="text-align: start;">future value of periodic payments</LI><LI style="text-align: start;">the number of periods with single sum investment</LI><LI style="text-align: start;">the number of periods with mutiple return investments</LI><LI style="text-align: start;">the interest rate for single sum and multiple return investments</LI></OL><P></P><P style="text-align: start;">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</P><P></P><P style="text-align: start;">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</P><P></P><P style="text-align: start;">Now back to the PV function in LiveCycle which accepts only three values</P><P></P><OL><LI style="text-align: start;">Periodic payment n1</LI><LI style="text-align: start;">Interest rate per period n2</LI><LI style="text-align: start;">Number of periods n3</LI></OL><P></P><P style="text-align: start;">The documentation makes it clear that the function will report an error if one were to pass negative values for n1 and n3</P><P></P><P style="text-align: start;">Say you have three values</P><P></P><P style="text-align: start;">Your base rent as n1 (must be positive) in LiveCycle </P><P style="text-align: start;">Your monthly interest rate n1 which is found by dividing the annual rate by 12</P><P style="text-align: start;">And number of months n3</P><P></P><P style="text-align: start;">n1 = 1000</P><P style="text-align: start;">n2 = 0.05/12</P><P style="text-align: start;">n3 = 5</P><P></P><P style="text-align: start;">thus LiveCycle PV function as follows</P><P></P><P style="text-align: start;">=PV(n1, n2, n3)</P><P></P><P>would be equivalent to Excel PV function as follows</P><P></P><P>=PV(n2, n3, -n1, 0, 0)</P><P></P><P>But you see this result would mean that the first payment is discounted at time period 1 </P><P></P><P>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</P><P></P><P>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 </P><P></P><P>Thus this in LiveCycle would give the present value of annuity due where the first payment is left undiscounted</P><P></P><P>=PV(n1,n2,n3) * (1+n2)</P><P></P><P>The (1+n2) is the interest factor that brings back the present value of an ordinary annuity to time period 0</P><P></P><P>Now if you wanted the same functionality of Excel PV function in LiveCycle then you would have to write your own code</P><P></P><P>I will give you the TVM equation and you would go ahead and convert it to LiveCycle user defined functions the same as Excel</P><P></P><P>The TVM equation is </P><P></P><P>FV * (1+r)^-N + PMT* (1+r*TYPE)* [1-(1+r)^-N]/r + PV = 0</P><P></P><P>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. </P><P></P><P>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</P><P></P><P>Good Luck with your efforts in getting LiveCycle PV function to solve your immediate concerns</P><P></P><P>Message was edited by: FinancialEngineer</P></BODY></HTML>Fri, 29 Mar 2013 12:49:58 GMThttps://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114110#M44479Deleted Account2013-03-29T12:49:58ZRe: FormCalc script language for calculating PV
https://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114111#M44480
<HTML><HEAD></HEAD><BODY><P>FinEng, <SPAN style="text-decoration: underline;"><STRONG>Thank you SO MUCH</STRONG></SPAN>!</P><P></P><P>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! <span class="lia-unicode-emoji" title=":slightly_smiling_face:">🙂</span></P><P></P><P>I will wave if/when I figure out how to make this work in my form, and share what finally works.</P><P></P><P>Appreciatively,</P><P>Shu</P></BODY></HTML>Fri, 29 Mar 2013 13:45:12 GMThttps://experienceleaguecommunities.adobe.com/t5/adobe-livecycle-discussions/formcalc-script-language-for-calculating-pv/m-p/114111#M44480LEShumaker2013-03-29T13:45:12Z