{"text_format":"1","workbook":"xlsx","n_sheets":17,"sheets":["Data Input","Gen Extra Info","Implicit Rate info","How to Enable Macros","Example - PV Calc","Lease 1 - PV Calc","Lease 2 - PV Calc","Lease 3 - PV Calc","Lease 4 - PV Calc","Lease 5 - PV Calc","Lease 6 - PV Calc","Lease 7 - PV Calc","Lease 8 - PV Calc","Lease 9 - PV Calc","Lease 10 - PV Calc","VLookup","Drop Down List"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":2,"defined_names":40,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Data Input","index":0,"n_rows":183,"n_cols":12,"rows_kept":183,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["GASB 87 Leases - Collection Template for Receivable Leases (State of AZ as LESSOR) for Conversion"]
[" ----- Due Date April 1, 2022     -----"]
["This FILLABLE lease template is designed to capture your agency's GASB 87 lease information for conversion."]
["All of the leases that you will report on this template should be the GASB 87 Leases that you identified on the \"GASB 87 LESSOR Decision Form for Conversion\".  NOTE - this template is for GASB 87 leases already ACTIVE as of 06/30/2021.  We will provide a separate Template for NEW leases entered into 07/01/2021 and later."]
[]
["Reporting background:   "]
["Please complete this template for every lease that was identified as a GASB 87 Lease on the \"GASB 87 LESSOR Decision Form for Conversion\" (Decision Form).  For example:\n   - Receivable leases where the State is the LESSOR\n   - Meets scope requirements and is not listed as specifically EXCLUDED,\n   - Total outstanding payments to be received as of 07/01/2021 are greater than the Materiality Threshold of $500,000, and\n   - Has a Maximum Possible Lease Term of more than 12 months."]
[]
["STEPS:"]
["1.  Collect the Lease Contracts and other information for all Leases ACTIVE as of 06/30/2021 that were identified as\n     GASB 87 Leases in the Decision Form."]
["2.  Save a copy of this Lease Template and rename with your Agency's 3 letter ID\n     (for example, \"ADA - GASB 87 LESSOR Collection Template for Conversion\")."]
["3.  Complete the following sections by entering in the applicable information for each lease within a separate column\n     (starting with column C).  There are 10 columns provided.  If you have more than 10 GASB 87 Receivable Leases,\n     duplicate the template & label as needed (1 of 2, 2 of 2, or 1 of 3, 2 of 3, 3 of 3, etc)."]
["4.  Maintain all documentation and supporting workpapers to support your conclusion for audit purposes in a permanent file (i.e. Lease Contracts, Decision Form, and this Lease Template)."]
["5.  Return a copy of this Decision Form to the GASB 87 e-mail address at right by the Due Date listed above (copy and paste\n      address).","GAOGASB87@azdoa.gov "]
[]
["Note 1 - The Blue highlighted cells are for manual or drop-down input. The Orange highlighted cells contain formulas and will automatically fill. Please do not change the orange cells."]
["Note 2 - Do not insert new columns or rows.  As noted above, if you have more than 10 leases, duplicate the form instead of inserting new columns."]
["Note 3 - Several sections below mention the criteria, 'Reasonably Certain'. To be reasonably certain means that there is a greater than probable chance an event will take place."]
[]
["Additional Resources can be found on the GAO website:","GAO Lease Resources"]
[]
["For additional questions on any of the above steps (including filling out this form), please contact:"]
["Tami Schuler - phone (602) 542-6248 or e-mail address at right (copy and paste address)","Tami.Schuler@azdoa.gov"]
["Janette Wallin - phone (602) 542-6224 or e-mail address at right (copy and paste address)","Janette.Wallin@azdoa.gov"]
[]
["Agency Information"]
["(Only need to fill out this section once for entire worksheet)"]
["Agency three letter ID (Example: Arizona Department of Administration = ADA):"]
["Preparer Name:"]
["Preparer E-mail:"]
["Preparer Phone:"]
["Date Completed:"]
["Approved by:"]
[]
["How many GASB 87 Receivable Leases were identified in the \"GASB 87 LESSOR Decision Form for Conversion\"?\nIf more than 10, the agency will need to fill out multiple forms (label them 1 of 2, 2 of 2,  or 1 of 3, 2 of 3, 3 of 3, etc)"]
[]
["General Information","Example (complex)","Lease # 1","Lease # 2","Lease # 3","Lease # 4","Lease # 5","Lease # 6","Lease # 7","Lease # 8","Lease # 9","Lease # 10"]
[]
["Contract # (If unknown or unassigned, please create your own.)","ABC 123"]
[]
["Name of the party you are leasing to:"]
["Enter the name of the lessee","ABC Land Development"]
[]
["Address of the Leased Property or Description of Leased Asset"]
["If leasing out office space, land or right-of-way, enter the full address of the leased property (for example:  1255 W. Bean St., Good Town, AZ 00000).  For equipment or something else, provide a description of the leased asset (for example:  HP 4700 Copier or 2021 Ford F150 truck, VIN #ABC123).","1234 Beaver St, Suite 300, Mayberry, AZ  86123"]
[]
["Is the lease any of the following (for disclosure only):"]
["Sublease - initially the State agency leases an asset from a third party, but then leases the asset out to another party. Example:  A state agency leases a floor in a building from a vendor and rents a room to another entity. The State acts as both Lessee and Lessor.\nSince the other parties involved are 2 different entities, both sides of the lease must be reported separately and should NOT be netted together.","No"]
["Sale-leaseback - the sale of a property with an agreement that the property will then be leased back.  Example:  A non-state entity sells a piece of property to a state agency with the contractual agreement that the non-state entity will lease the asset back. The state agency is the Purchasor and the Lessor.","No"]
["Lease-leaseback - the lease of a property to a tenant with the agreement that the property will then be leased back from that same party. Example: A state agency owns a piece of land and leases it to a non-state company. The agreement obligates the company to build an office and lease it back to the state agency.  The State acts as both Lessee and Lessor.\nIn this case, since the other party is the same entity, the lease proceeds and payouts will be netted for reporting purposes.","No"]
["If any of the 3 questions above are answered Yes, you will be contacted by the GAAP Group for additional info."]
[]
["Is the entire lease payment received recorded 100% into a single Chart of Accounts string (COA) or Divided between Funds or Agencies?","Divided - Fill out Gen Extra Info Tab"]
["If the receipt of payment is divided, then must switch to the \"Gen Extra Info\" Tab to fill out the deposit split info (click on hyperlink to the right for correct corresponding Lease # column).","Example - Gen Extra Info","Lease # 1 - Gen Extra Info","Lease # 2 - Gen Extra Info","Lease # 3 - Gen Extra Info","Lease # 4 - Gen Extra Info","Lease # 5 - Gen Extra Info","Lease # 6 - Gen Extra Info","Lease # 7 - Gen Extra Info","Lease # 8 - Gen Extra Info","Lease # 9 - Gen Extra Info","Lease # 10 - Gen Extra Info"]
["If the receipt of payment is recorded into a single COA, please provide the following COA Elements below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the receipt of payments for each lease."]
["Agency *","see Gen Extra Info Tab"]
["Fund *"]
["Revenue Source *"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
[]
["Asset Information"]
[]
["Asset type that is being leased out"]
["Using the drop down list, select the Asset Type that is being leased. If the leased asset type is not listed, please choose a description that is closest to your leased asset (or contact the GAAP Group for further assistance).","Leased Constructed Buildings (Office Space) "]
[]
["Return of the Asset"]
["Does the contract require the lessee to return the asset in its original or enhanced condition?","Yes"]
["If above question is answered Yes, then GASB 87 requires that the asset accumulate NO DEPRECIATION during the lease period as it is expected to be returned in the original or enhanced condition.  If, however, there is a residual value guarantee set at a value lower than its original value at commencement of the lease, depreciation should still be calculated.  If the asset is Land, then there should not be any depreciation regardless."]
[]
["What is the asset number listed on the AFIS Fixed Asset Module (if not in AFIS, please explain)?","AD1000"]
[]
["Lease Term"]
[]
["Lease commencement date"]
["For existing leases being converted, start date is 07/01/2021 (as noted above, this template is for active leases on or before 06/30/2021 only - we will provide a separate template for new leases entered into during FY2022).","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01","2021-07-01"]
[]
["Noncancellable term"]
["Enter the time period in months that the lease cannot be cancelled by either party."]
["For leases already in progress, the remaining noncancellable period as of 07/01/2021 will need to be reduced depending on how long it has been already been active and could be 0.",0]
[]
["Termination Option"]
["Is there a termination option available?","No"]
[]
["If yes, does the agency expect it is reasonably certain to be exercised?","N/A"]
["If Yes, use the date that the agency is expecting the termination option to be exercised for the Lease End Date requested in row 95 below.."]
[]
["Remaining contracted lease term"]
["Lease End Date - Enter the date the contracted lease term ends, or the expected end date (if the termination option is expected to be exercised)","2023-12-31"]
[]
["Noncancellable Term + Remaining Lease Term in months (will automatically fill)",{"$f":"ROUND(DATEDIF((B81-1),B94,\"M\"),0)","v":30},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"}]
["Options to extend term"]
["Does the lease offer extensions that the agency is reasonably certain will be exercised (Do NOT include month-to-month options)?","Yes"]
[]
["If so, enter # of months that either the agency or lessee has the option to extend the lease (again, only if it is reasonably certain that it will be exercised).",12]
[]
["Lease End Date including Extensions (use date from row 94 & add number of months from row 100).","2024-12-31"]
[]
["Total Remaining Lease Term including Extensions at 07/01/2021 in Months (will automatically fill)",{"$f":"B96+B100","v":42},{"$f":"C96+C100","v":{"$error":"#NUM!"}},{"$f":"D96+D100","v":{"$error":"#NUM!"}},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"},{"$error":"#NUM!"}]
[]
["Frequency of lease payments"]
["Enter how often lease payments are received (for example - Monthly, Quarterly, Semi-Annually, Annually)","Quarterly"]
[null,{"$f":"IF(B107=\"Weekly\",52,IF(B107=\"Monthly\",12,IF(B107=\"Quarterly\",4,IF(B107=\"Semi-Annually\",2,1))))","v":4},{"$f":"IF(C107=\"Weekly\",52,IF(C107=\"Monthly\",12,IF(C107=\"Quarterly\",4,IF(C107=\"Semi-Annually\",2,1))))","v":1},{"$f":"IF(D107=\"Weekly\",52,IF(D107=\"Monthly\",12,IF(D107=\"Quarterly\",4,IF(D107=\"Semi-Annually\",2,1))))","v":1},1,1,1,1,1,1,1,1]
[]
["How many payments are remaining to be received (from 07/01/2021 to lease end date including extensions (row 102)?"]
["If payments are monthly, this answer will most likely be the same as the calculated number of months remaining (row 104, but should probably check carefully).",14]
[]
["Was the first lease payment received at the beginning or at the end of the first payment period?"]
["For example, lease began on 4/01 with monthly payments and first payment was due 4/30, then it would be the End of the first payment period.","Begin"]
[null,{"$f":"IF(B114=\"Begin\",1,0)","v":1},{"$f":"IF(C114=\"Begin\",1,0)","v":0},{"$f":"IF(D114=\"Begin\",1,0)","v":0},0,0,0,0,0,0,0,0]
[]
["When is the first payment due for FY2022 (most likely 07/01/2021 or 07/31/2021, but could be different if not monthly payments).","2021-07-01"]
[]
["Lease Receivable"]
[]
["Lease payment to be received:"]
["Does the lease payment amount remain the same throughout the entire lease (including extensions)?","No - Fill out PV Calc Tab"]
["If No, please provide each lease payment & duration in the \"PV Calc\" tab that matches the Lease # above.  This is necessary as the lease is more complex than this Data Input sheet can handle, and manual calculations will need to be completed."]
["If Yes, please fill in the Fixed Lease portion of the payment below."]
[]
["Fixed Lease portion of payment only:"]
["(1) Enter the amount of the fixed lease portion of the payment.  If the payment includes utilities, taxes, service costs,\n      etc, and it is not reasonable to separate these portions from the total payment, then enter the total lease\n      payment.  If the above listed items are separated in the contract, they should not be included in the lease\n      payment here but detailed below.\n(2) In addition, also include \"Fixed In-Substance Fees\" with the lease payment (see definition below under variable\n      payments).\n(3) For payments that depend on an index or a rate (such as the Consumer Price Index or a market interest rate),\n      the payment should be initially measured using the index or rate as of 07/01/2021.","see PV Calc tab"]
[]
[" Variable Usage Fees over and above Fixed In-Substance Fees:"]
["\"Fixed in-Substance Fees\" are the Mimimum Usage Costs of a variable usage fee and should be included in the fixed lease payment portion above.  For Example, if a copier has a $.01/copy variable monthly fee, but minimum is $500, then $500 is the \"Fixed In-Substance Fee\" and should be added to fixed lease portion above, and the variable answer here would be \"$.01/copy over 50,000 copies due to $500 minimum\" ($500 is equal to $.01/copy * 50,000).","N/A"]
[]
["Please describe any other known payments to be received through the lease contract (service/maintenance, utilities, taxes, etc).","N/A"]
[]
["Residual Value Guarantee:"]
["Does the lease contain a residual value guarantee?"]
["For example, does the lease specify the asset must be returned in a certain condition to avoid a penalty, or must have a certain minimum fair value upon return to the lessor?","No"]
[]
["If so, is the residual value guarantee payment fixed in substance? (i.e. not contingent on a future event)."]
["For example, a residual value guarantee could have both a minimum amount to be paid upon termination of the lease plus a contingent fee based on the future value of the asset upon termination of the lease.  If the residual value guarantee has no minimum, but is fully contingent on the future value, then it is not fixed in substance and should NOT be included in the Lease Receivable calculation.\nThis is different than on a payable lease where the inclusion in the liability is based on a reasonably certain occurence, not a fixed in substance occurrence.","N/A"]
["If Yes, then please enter the amount of the Fixed in Substance Residual Value Guarantee on the PV Calc Tab."]
[]
["Termination option (for disclosure only):"]
["If a termination clause is identified in the lease, please describe the terms (N/A if not applicable)"]
["For example, \"lessee may terminate lease with a 6-month notice without penalty\".","lessee may terminate lease upon a 60-day notice & payment of penalty"]
[]
["If the termination clause includes a penalty if exercised, how much is the penalty (N/A if not applicable)?",1500]
["This amount is NOT included in the calculation of the Lease Receivable, but is only requested here for disclosure purposes (again this is different than payable leases)."]
[]
["Lease incentives:"]
["Does the lease agreement contain lease incentives, such as Abated or Free Rent periods, Leasehold Improvements paid for by the lessor, etc?","Yes"]
[]
["If Yes, please describe:","Free Rent Quarter for\n3-6/2022"]
[]
["Does Lease Incentive include a Change or Reduction in one or more lease payments, or a payment to the lessee from the lessor?","Yes - Fill out PV Calc Tab"]
["If Lease Incentive includes a change or reduction (such as a free rent period) in one or more lease payments or a payment from the lessor to the lessee, then must switch to the \"PV Calc\" tab to include the change in the payment for calculation of the Present Value."]
[]
["Estimated Uncollectible Amounts"]
["Based on current economic factors, lessee's credit rating, historical information from this or other lessees, does the agency feel that there may be uncollectible amounts related to this lease agreement?","Yes - Fill out PV Calc Tab"]
[]
["Interest Rate"]
["▪ Enter the interest rate for lease if identified in the contract (presented with four digits past the decimal)"]
["▪ If the interest rate is unknown, there may be an implicit rate  (please see tab, \"Implicit Rate info\" for explanation)"]
["▪ If both the explicit interest rate and implicit rate are unknown, identify the interest rate in the table below using the REMAINING lease term as of 07/01/2021 (refer to the total remaining lease term in row 104 above & convert to years)"]
["For example: If contract initiated on 04/01/2017, original term of 120 months (10 years),  remaining term as of 7/01/2021 is 69 months (5 3/4 years), so the interest rate would be 1.1754%",7.2779999999999997E-3]
[]
[]
[]
[]
[]
[]
[]
["Short-Term in months = 0 - 48 months"]
["Mid-Term in months = 49 - 96 months"]
["Long-Term in months = 97 months or longer"]
[]
["Periodic Interest Rate - based on frequency of payments (will automatically fill)",{"$f":"ROUND(B164/B108,6)","v":1.82E-3},{"$f":"ROUND(C164/C108,6)","v":0},{"$f":"ROUND(D164/D108,6)","v":0},{"$f":"ROUND(E164/E108,6)","v":0},{"$f":"ROUND(F164/F108,6)","v":0},{"$f":"ROUND(G164/G108,6)","v":0},{"$f":"ROUND(H164/H108,6)","v":0},{"$f":"ROUND(I164/I108,6)","v":0},{"$f":"ROUND(J164/J108,6)","v":0},{"$f":"ROUND(K164/K108,6)","v":0},{"$f":"ROUND(L164/L108,6)","v":0}]
[]
["Calculate the Lease Liability"]
[]
["Did the answer to any of the questions above instruct you to Fill out PV Calc Tab (No to the equal Lease Payment question, or Yes to the Residual Value Guarantee, Lease Incentive if change to payment or Estimated Uncollectible Amounts questions)?","Yes - Fill out PV Calc Tab"]
["If Yes, make sure the \"PV Calc\" tab corresponding to the correct Lease # (Lease # 1, Lease # 2, etc) is filled out fully."]
[]
["The Present Value and Amortization Tables will be calculated in each respective PV Calc tab.  These are hyperlinked in each column to the right - just point your mouse at the corresponding column \"Lease XX - PV Calc\" and click.","Example - PV Calc","Lease 1 - PV Calc","Lease 2 - PV Calc","Lease 3 - PV Calc","Lease 4 - PV Calc","Lease 5 - PV Calc","Lease 6 - PV Calc","Lease 7 - PV Calc","Lease 8 - PV Calc","Lease 9 - PV Calc","Lease 10 - PV Calc"]

{"sheet":"Gen Extra Info","index":1,"n_rows":122,"n_cols":12,"rows_kept":122,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["GASB 87 Lease Collection Template - General Extra Info - for Leases receiving payments into multiple COAs"]
[]
["If the lease payments received are recorded within more than one COA, please complete the information below. "]
["Note - The blue highlighted cells are for manual or drop-down input. The orange highlighted cells contain formulas and will automatically fill. Please do not change the orange cells."]
[]
["Use the SAME COLUMN here as was used in the \"Data Input\" tab.","Example","Lease # 1","Lease # 2","Lease # 3","Lease # 4","Lease # 5","Lease # 6","Lease # 7","Lease # 8","Lease # 9","Lease # 10"]
[]
["When payments are split between multiple COAs, please ONLY provide split information for the Fixed amount to be received.  Please exclude any variable payments or payments for services, maintenance, taxes, etc that are identified separately within the lease contract."]
["Is the payment received divided by a Percentage (%), or a Specific Dollar ($) Amount (i.e. 50% posted to one COA string, and 50% posted to another; or first $1,000 posted to one COA string and the rest posted to another)?","Percentage (%) Split"]
["Based on your answer, please fill out the proper section below (Percentage Split or Dollar Amount Split)"]
[]
["Percentage (%) Split:"]
["How many different sets of COAs are there?",2]
[]
["1 - Percentage of payment received for First COA",0.7]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["1a - Agency *","ADA"]
["1b - Fund *","AD1234"]
["1c - Revenue Source *",4632]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
[]
["2 - Percentage of payment received for Second COA",0.3]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["2a - Agency *","DCA"]
["2b - Fund *","DC4567"]
["2c - Revenue Source *",4632]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
[]
["3 - Percentage of payment received for Third COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Revenue Source *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
[]
["4 - Percentage of payment received for Fourth COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Revenue Source *"]
["4d - Appropriation"]
["4e - Unit "]
["4f - Task "]
["4g - Sub Task"]
["4h - Program"]
["4i - Phase"]
["4j - Program Period"]
["4k - Dept Object"]
[]
["Specific Dollar ($) Amount Split:"]
["How many different sets of COAs are there?"]
[]
["1 - Amount of payment received for First COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["1a - Agency *"]
["1b - Fund *"]
["1c - Revenue Source *"]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
[]
["2 - Amount of payment received for Second COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["2a - Agency *"]
["2b - Fund *"]
["2c - Revenue Source *"]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
[]
["3 - Amount of payment received for Third COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Revenue Source *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
[]
["4 - Amount of payment received for Fourth COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Revenue Source *"]
["4d - Appropriation"]
["4e - Unit "]
["4f - Task "]
["4g - Sub Task"]
["4h - Program"]
["4i - Phase"]
["4j - Program Period"]
["4k - Dept Object"]

{"sheet":"Implicit Rate info","index":2,"n_rows":28,"n_cols":12,"rows_kept":28,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["Calculating an Implicit Rate on a Lease:"]
[]
["If an interest rate is not explicitely stated within the lease contract, there may be an implicit rate, which can be calculated if the information requested below is available."]
[]
["In addition to the information provided within the Data Input tab (see orange auto-filled cells below), check the lease contract for availability of the 3 additional data items requested below to determine the Implicit Rate."]
[]
["All 3 items requested below are needed to calculate the Implicit Rate.  If this information is unavailable, then use the Discount Rates provided on the Data Input tab (yellow highlighted table)."]
[]
["If ALL of the information requested below is available for a lease, please enter the information within the correct Lease # column below."]
[]
["Note - The blue highlighted cells are for manual input. The orange highlighted cells contain formulas and will automatically fill. Please do not change the orange cells."]
[]
["Use the SAME COLUMN here as was used in the \"Data Input\" tab.","Example","Lease # 1","Lease # 2","Lease # 3","Lease # 4","Lease # 5","Lease # 6","Lease # 7","Lease # 8","Lease # 9","Lease # 10"]
[]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!B114","v":"Begin"},{"$f":"'Data Input'!C114","v":0},{"$f":"'Data Input'!D114","v":0},{"$f":"'Data Input'!E114","v":0},{"$f":"'Data Input'!F114","v":0},{"$f":"'Data Input'!G114","v":0},{"$f":"'Data Input'!H114","v":0},{"$f":"'Data Input'!I114","v":0},{"$f":"'Data Input'!J114","v":0},{"$f":"'Data Input'!K114","v":0},{"$f":"'Data Input'!L114","v":0}]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!B111","v":14},{"$f":"'Data Input'!C111","v":0},{"$f":"'Data Input'!D111","v":0},{"$f":"'Data Input'!E111","v":0},{"$f":"'Data Input'!F111","v":0},{"$f":"'Data Input'!G111","v":0},{"$f":"'Data Input'!H111","v":0},{"$f":"'Data Input'!I111","v":0},{"$f":"'Data Input'!J111","v":0},{"$f":"'Data Input'!K111","v":0},{"$f":"'Data Input'!L111","v":0}]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!B107","v":"Quarterly"},{"$f":"'Data Input'!C107","v":0},{"$f":"'Data Input'!D107","v":0},{"$f":"'Data Input'!E107","v":0},{"$f":"'Data Input'!F107","v":0},{"$f":"'Data Input'!G107","v":0},{"$f":"'Data Input'!H107","v":0},{"$f":"'Data Input'!I107","v":0},{"$f":"'Data Input'!J107","v":0},{"$f":"'Data Input'!K107","v":0},{"$f":"'Data Input'!L107","v":0}]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!B117","v":"2021-07-01"},{"$f":"'Data Input'!C117","v":"00:00:00"},{"$f":"'Data Input'!D117","v":"00:00:00"},{"$f":"'Data Input'!E117","v":"00:00:00"},{"$f":"'Data Input'!F117","v":"00:00:00"},{"$f":"'Data Input'!G117","v":"00:00:00"},{"$f":"'Data Input'!H117","v":"00:00:00"},{"$f":"'Data Input'!I117","v":"00:00:00"},{"$f":"'Data Input'!J117","v":"00:00:00"},{"$f":"'Data Input'!K117","v":"00:00:00"},{"$f":"'Data Input'!L117","v":"00:00:00"}]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!B102","v":"2024-12-31"},{"$f":"'Data Input'!C102","v":"00:00:00"},{"$f":"'Data Input'!D102","v":"00:00:00"},{"$f":"'Data Input'!E102","v":"00:00:00"},{"$f":"'Data Input'!F102","v":"00:00:00"},{"$f":"'Data Input'!G102","v":"00:00:00"},{"$f":"'Data Input'!H102","v":"00:00:00"},{"$f":"'Data Input'!I102","v":"00:00:00"},{"$f":"'Data Input'!J102","v":"00:00:00"},{"$f":"'Data Input'!K102","v":"00:00:00"},{"$f":"'Data Input'!L102","v":"00:00:00"}]
[]
["1. Fair value of the asset at commencement "]
[]
["2. Lessor's expected fair value of the asset at the end of the lease term"]
[]
["3. Any initial direct costs that the lessor incurs"]
[]
["IF THE IMPLICIT RATE INFO IS FILLED OUT ABOVE, please enter \"See Implicit Rate Info tab\" within the interest rate cell (row 192) on the Data Input tab."]
["With the information provided above, the GAO office will calculate the Implicit Rate and the Present Value of the Lease."]

{"sheet":"How to Enable Macros","index":3,"n_rows":5,"n_cols":1,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["YOUR EXCEL TRUST CENTER SETTINGS MUST BE MACRO-ENABLED TO RUN THE MACROS IN THIS FILE."]
["(This file was created in Excel Version 2016)"]
["To enable settings:"]
["Go to File > Options > Trust Center > Trust Center Settings… "]
["Enable all macros and trust access to the VBA project object model as shown below."]

{"sheet":"Example - PV Calc","index":4,"n_rows":124,"n_cols":16,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Example (column B)","Orange highlighted cells will automatically fill",null,null,"Present Value Table",null,null,null,null,"Amortization Table"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!B176","v":1.82E-3},null,null,"Period","Payment Amount","Present Value",null,null,"Period","Date","Beginning Balance","Payment","Principal","Interest","Ending Balance"]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!B114","v":"Begin"},"Important:",null,1,45000,{"$f":"F3 / (((1+$B$2)) ^ (E3-1))","v":45000},null,null,1,{"$f":"B6","v":"2021-07-01"},{"$f":"B25","v":589610.93703124695},45000,{"$f":"M3-O3","v":45000},0,{"$f":"L3-N3","v":544610.93703124695}]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!B111","v":14},"Do not insert/delete/move",null,2,45000,44918.248787207289,null,null,2,{"$f":"EDATE($B$6,3)","v":"2021-10-01"},{"$f":"P3","v":544610.93703124695},45000,44008.81,{"$f":"ROUND(L4*$B$2,2)","v":991.19},500602.12703124696]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!B107","v":"Quarterly"},"cells B2:B7",null,3,45000,44836.646091321083,null,null,3,{"$f":"EDATE($B$6,6)","v":"2022-01-01"},500602.12703124696,45000,44088.9,911.1,456513.22703124693]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!B117","v":"2021-07-01"},"(macros depend on them)",null,4,0,0,null,null,4,{"$f":"EDATE($B$6,9)","v":"2022-04-01"},456513.22703124693,0,-830.85,830.85,457344.07703124691]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!B102","v":"2024-12-31"},null,null,5,45000,44673.885171519505,null,null,5,{"$f":"EDATE($B$6,12)","v":"2022-07-01"},457344.07703124691,45000,44167.63,832.37,413176.44703124691]
[null,null,null,null,6,45000,44592.726409454306,null,null,6,{"$f":"EDATE($B$6,15)","v":"2022-10-01"},413176.44703124691,45000,44248.02,751.98,368928.42703124689]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments",null,7,45000,44511.715087994162,null,null,7,{"$f":"EDATE($B$6,18)","v":"2023-01-01"},368928.42703124689,45000,44328.55,671.45,324599.8770312469]
["Current Lease Payment Amount & # of Payments remaining at this amount",45000,3,null,8,45000,44430.850939284661,null,null,8,{"$f":"EDATE($B$6,21)","v":"2023-04-01"},324599.8770312469,45000,44409.23,590.77,280190.64703124692]
["Payment Amount #2 & # of Payments at this amount",0,1,null,9,45000,44350.133695958008,null,null,9,{"$f":"EDATE($B$6,24)","v":"2023-07-01"},280190.64703124692,45000,44490.05,509.95,235700.59703124693]
["Payment Amount #3 & # of Payments at this amount",45000,6,null,10,45000,44269.56309113215,null,null,10,{"$f":"EDATE($B$6,27)","v":"2023-10-01"},235700.59703124693,45000,44571.02,428.98,191129.57703124694]
["Payment Amount #4 & # of Payments at this amount",48000,4,null,11,48000,47135.081448970508,null,null,11,{"$f":"EDATE($B$6,30)","v":"2024-01-01"},191129.57703124694,48000,47652.14,347.86,143477.43703124695]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0},null,12,48000,47049.451447336345,null,null,12,{"$f":"EDATE($B$6,33)","v":"2024-04-01"},143477.43703124695,48000,47738.87,261.13,95738.567031246959]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":597000},{"$f":"SUM(C10:C14)","v":14},null,13,48000,46963.977009179645,null,null,13,{"$f":"EDATE($B$6,36)","v":"2024-07-01"},95738.567031246959,48000,47825.760000000002,174.24,47912.807031246957]
[null,null,null,null,14,48000,46878.657851889213,null,null,14,{"$f":"EDATE($B$6,39)","v":"2024-10-01"},47912.807031246957,48000,47912.81,{"$f":"ROUND(L16*$B$2,2)-0.01","v":87.19},-2.9687530404771678E-3]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,{"$f":"ROUND(B15*-0.002,0)","v":-1194}]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":597000}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":589610.93703124695},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 1 - PV Calc","index":5,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 1 (column C)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!C176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!C114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!C111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!C107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!C117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!C102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 2 - PV Calc","index":6,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 2 (column D)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!D176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!D114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!D111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!D107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!D117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!D102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 3 - PV Calc","index":7,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 3 (column E)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!E176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!E114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!E111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!E107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!E117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!E102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 4 - PV Calc","index":8,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 4 (column F)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!F176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!F114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!F111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!F107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!F117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!F102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 5 - PV Calc","index":9,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 5 (column G)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!G176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!G114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!G111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!G107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!G117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!G102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 6 - PV Calc","index":10,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 6 (column H)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!H176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!H114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!H111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!H107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!H117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!H102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 7 - PV Calc","index":11,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 7 (column I)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!I176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!I114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!I111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!I107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!I117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!I102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 8 - PV Calc","index":12,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 8 (column J)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!J176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!J114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!J111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!J107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!J117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!J102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 9 - PV Calc","index":13,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 9 (column K)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!K176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!K114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!K111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!K107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!K117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!K102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"Lease 10 - PV Calc","index":14,"n_rows":124,"n_cols":3,"rows_kept":124,"cut":false,"hidden":false,"merged":[]}
["Present Value Calculations tab - Lease 10 (column L)","Orange highlighted cells will automatically fill"]
["Periodic Interest Rate (rows 160-176 from Data Input tab)",{"$f":"'Data Input'!L176","v":0}]
["Cash Flows at Beginning or End (row 114 of Data Input tab)",{"$f":"'Data Input'!L114","v":0},"Important:"]
["Number of payments (row 111 of Data Input tab)",{"$f":"'Data Input'!L111","v":0},"Do not insert/delete/move"]
["Frequency of payments (row 107 of Data Input tab)",{"$f":"'Data Input'!L107","v":0},"cells B2:B7"]
["Date of first payment (row 117 of Data Input tab)",{"$f":"'Data Input'!L117","v":"00:00:00"},"(macros depend on them)"]
["Lease End Date including Extensions (row 102 of Data Input tab)",{"$f":"'Data Input'!L102","v":"00:00:00"}]
[]
["Lease Payment Receivable Schedule (manually fill blue cells):","Amount","# of Payments"]
["Current Lease Payment Amount & # of Payments remaining at this amount",0,0]
["Payment Amount #2 & # of Payments at this amount",0,0]
["Payment Amount #3 & # of Payments at this amount",0,0]
["Payment Amount #4 & # of Payments at this amount",0,0]
["Totals from add'l table below if needed)",{"$f":"B75","v":0},{"$f":"C75","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B10*C10)+(B11*C11)+(B12*C12)+(B13*C13)+(B14)","v":0},{"$f":"SUM(C10:C14)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):","Residual Value Guar","Allow for Uncollectible Amt"]
["Residual Value Guarantee if Fixed-in-Substance (rows 134-140 of Data Input tab)",0]
["Estimated Uncollectible Amount - should be a negative amount (rows 157-158)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0]
["Total Other Payment Considerations",{"$f":"SUM(B18:B20)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B15+B21","v":0}]
[]
[null,null,"Important:"]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":0},"Do not insert/delete/move"]
[null,null,"NPV must be cell B25"]
[null,null,"(macros depend on it)"]
[]
["The Agency is not required to calculate the Present Value and/or enable Macros,"]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed."]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:"]
[]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab"]
["Note 2:  Please do not Insert, Delete, or Move any cells, columns or rows as this could cause the Macros to fail."]
["Note 3:  The Blue Cells in columns B & C are for manual input, and the Present Value Table will need to be adjusted in column F."]
["The Orange cells in columns B & C will automatically fill.  Also, the Present Value and Amortization Tables will be generated with the"]
["Macro buttons.  Please do not make changes to the orange cells or anywhere within the Tables (except column F)."]
[]
["Step 1 - Fill out the \"Lease Payment Receivable Schedule\" section above (starting on row 9)."]
["a - If payments escalate over time, list current payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract, indicate that with a $0 payment"]
["amount & the number of payments to be skipped.  Make sure to put these skipped payments in the correct order chronologically."]
["For example, if there is a Free Rent period for payment 13, then list the first 12 payments on the first line, then line #2 as $0 (Amount"]
["in column B) and 1 (# of payments to be skipped in column C), then line #3 as the continuing payments after the Free Rent period."]
["e - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and then input the"]
["totals from the additional table in the Payment #5 line above."]
["f - Verify that the total # of payments in cell B4 is the same as the sum of column C (cell C15)."]
[]
["Additional Lease Payment Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Receivables (fill in totals on the Payment #5 line above)",{"$f":"(B54*C54)+(B55*C55)+(B56*C56)+(B57*C57)+(B58*C58)+(B59*C59)+(B60*C60)+(B61*C61)+(B62*C62)+(B63*C63)+(B64*C64)+(B65*C65)+(B66*C66)+(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)","v":0},{"$f":"SUM(C54:C74)","v":0}]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 17) ONLY if instructed to do so on the Data Input tab."]
["a - The Residual Value Guarantee line should only have an amount listed in Column B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Amount line should only have an amount listed in column C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in column C because it"]
["will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is calculated."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into column F of the PV Table."]
["a - If there are 12 payments for the 1st payment amount, and 6 payments for the 2nd payment amount, list the 1st amount in the"]
["first 12 rows of the Table, then the 2nd amount for the next 6 rows, and do the same for the remaining payment amounts (3-5)"]
["listed in the Payment Schedule until all the payments are filled in on the Table."]
["b - Make sure not to skip any Free Rent periods.  If the Payment Schedule indicates the 13th payment is $0, then row 13 of the"]
["Table should be $0, same as the Payment Schedule."]
["c - If there are any amounts listed for \"Other Payment Considerations\", add it to the final payment.  For example, if the final"]
["payment is $20,000, and there is a Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,000."]
["d - Highlight all of the payments in column F of the Table.  The sum of the highlighted cells should be displayed in the lower right"]
["corner of the spreadsheet.  Check to make sure that amount is the same as the Total Undiscounted Cash Flows in cell B22."]
[]
["Step 5 - Click the Macro button below to Calculate Net Present Value and Generate the PV and Amortization Tables."]
[]
[]
[]
["Step 6 - Check data for accuracy:"]
["a - Check the date of the final payment in the Amortization Table and compare to Lease End Date in cell B7.  Make sure the final"]
["payment date is reasonable in comparison to the Lease End Date (within same month if payments are Monthly, within the final"]
["quarter if Quarterly, etc)."]
["b - If payments change (there are amounts in Payment Amount #2 - 5 rows of Payment Receivable Schedule), then check the dates the"]
["amounts change in the Amort Table (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 1f & check for Table payments in 4d."]
["ii - Double-check cells B2-B7 by reviewing the Lease Contract.  These cells automatically filled based on answers in the Data Input"]
["tab.  Make any necessary corrections in the Data Input tab (NOT in cells B2-B7) - the corresponding rows are referenced above."]
["iii - Double-Check the Payment Schedule and Other Payment Considerations sections.  Compare back to the Lease contract to"]
["verify, and correct as necessary."]
["e - Once corrections have been made, click the Macro button below to Delete the PV & Amortization Tables."]
[]
[]
[]
["Step 7 - Repeat Steps 3-6 as needed until all checks (1f, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25."]
["Also, see Present Value Table in columns E-G,"]
["and Amortization Table in columns J-P."]

{"sheet":"VLookup","index":15,"n_rows":29,"n_cols":4,"rows_kept":29,"cut":false,"hidden":false,"merged":[]}
["Asset Type","For",null,"Useful Life in Months"]
["Constructed Building ","Office Space",{"$f":"CONCATENATE(A2,\" - \",B2)","v":"Constructed Building  - Office Space"},480]
["Manufactured Building (non-permanent)","Office Space",{"$f":"CONCATENATE(A3,\" - \",B3)","v":"Manufactured Building (non-permanent) - Office Space"},300]
["Vehicles","Aircraft","Vehicles - Aircraft",180]
["Vehicles","Heavy Duty Truck","Vehicles - Heavy Duty Truck",84]
["Vehicles","Regular truck/car","Vehicles - Regular truck/car",60]
["Equipment","Furniture","Equipment - Furniture",60]
["Equipment","IT Equipment","Equipment - IT Equipment",60]
["Equipment","Telecommunications","Equipment - Telecommunications",60]
["Equipment","Other Office Equipment","Equipment - Other Office Equipment",60]
["Land",null,"Land","N/A"]
[null,null,"Right-of-way","N/A"]
[]
["Fixed Asset Catalog Description",null,"Useful Life in Months"]
["Leased Constructed Buildings (Office Space) ",null,480]
["Leased Buildings: Prefabricated (Office Space)",null,300]
["Leased Mainframe Computer",null,60]
["Leased Servers",null,60]
["Leased Desktop Computer",null,60]
["Leased Multifunction (print/scan/fax/copy) workgroup printer",null,60]
["Leased Airplane",null,180]
["Leased Automobiles & Station Wagons",null,60]
["Leased Trucks (Heavy)",null,84]
["Leased Mass Transportation: Transit Bus",null,84]
["Leased Radio, Sound and Telecomm. Equipment",null,60]
["Leased Land Improvements",null,480]
["Leased Improvements Other than Land & Building Improvements",null,180]
["Leased Tangible Rights of Way ",null,"Use contract term"]
["Leased Land ",null,"Use contract term"]

{"sheet":"Drop Down List","index":16,"n_rows":49,"n_cols":1,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
[]
["Asset Type"]
["Land"]
["Building"]
["Equipment"]
["Other"]
[]
["Payment System"]
["AFIS"]
["APP"]
[]
["Yes / No / N/A"]
["Yes"]
["No"]
["N/A"]
[]
[]
["Payment frequency"]
["Monthly"]
["Quarterly"]
["Semi-annually"]
["Annually"]
[]
["Payment B or E"]
["Begin"]
["End"]
[]
["Payments Equal?"]
["Yes"]
["No - Fill out PV Calc Tab"]
["N/A"]
[]
["Purchase Option / Termination Penalty/Residual Value Guarantee?"]
["Yes - Fill out PV Calc Tab"]
["No"]
["N/A"]
[]
["single / divided COA"]
["Single COA"]
["Divided - Fill out Gen Extra Info Tab"]
[]
["Type of Division"]
["Specific $ Amount Split"]
["Percentage (%) Split"]
[]
["Termination Option"]
["Yes - use Expected End Date for Lease End Date"]
["No"]
["N/A"]