{"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":1,"defined_names":30,"comments":1,"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":188,"n_cols":12,"rows_kept":188,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["STATE OF ARIZONA\nCLOSING PACKAGE\nN - GASB 87 LEASES - Form 71a\nCollection Form for FY2023 Payable Leases\n(State as LESSEE)"]
[" ----- Due Date 8/11/2023 -----"]
["This FILLABLE form is designed to capture your agency's GASB 87 lease information for NEW leases entered into during FY2023."]
[]
["Reporting background:   "]
["Please complete this template for every lease that qualifies as a GASB 87 lease AND has a start date during FY2023\n(July 1, 2022 - June 30, 2023).\nFor example:\n   - Meets scope requirements and is not listed as specifically EXCLUDED,\n   - Total lease payments (plus purchase options) are greater than the Materiality Threshold of $500,000, and\n   - Has a Maximum Possible Lease Term of more than 12 months.\nYou should have completed Form 71 - \"Decision Form for Lessee\" first to verify qualifications (link at right):","Closing Package Information -\nSection N - Leases"]
[]
["STEPS:"]
["1.  Collect the Lease Contracts and other information for all NEW Leases entered into during FY2023 and document whether they\n      qualify as GASB 87 Leases (either through the Decision Form or some other similar process).  If any leases qualify as GASB 87\n      Leases, then this Lease Collection Form must be completed."]
["2.  Save a copy of this Lease Collection Form and rename with your Agency's 3 letter ID\n     (for example, \"ADA Form 71a - Collection Form for Lessees - FY2023\")."]
["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 Payable Leases,\n      duplicate the form & 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\n     (i.e. Lease Contracts, Decision Form (or substitute), and this Lease Collection Form)."]
["6.  Return a copy of this Lease Collection Form to the ACFR e-mail address at right by the Due Date listed above (copy and paste\n      address).","ACFR@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 (including the Decision Form):","GAO Lease Resources"]
[]
["For additional questions on any of the above steps (including filling out this form), please send questions to the ACFR e-mail address above."]
[]
["I.  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 qualifying GASB 87 Payable Leases were entered into during FY2023?\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)"]
[]
["II.  General Information","Example (complex)","Lease # 1","Lease # 2","Lease # 3","Lease # 4","Lease # 5","Lease # 6","Lease # 7","Lease # 8","Lease # 9","Lease # 10"]
["1.  Contract # (For building or office space leases, contact Nola Barnes, Assistant Director, General Services Division, for your\n      Contract # if unknown.  For other types of leases where contract # is unknown, please create your own.)","KR12-3456"]
[]
["2.  Name of the Lessor (Vendor where payments are sent)","ABC Property Management, Inc."]
[]
["3.  Address of the Leased Property or Description of Leased Asset"]
["If leasing office space, land or right-of-way, enter the full address of the leased property.  For equipment or something else, provide a description of the leased asset (for example:  HP 4700 Copier on 3rd Floor or 2021 Ford F150 truck, VIN #ABC123).","1234 Beaver St, Suite 300, Mayberry, AZ  86123"]
[]
["4.  Is the lease any of the following (for disclosure only):"]
["a.  Sublease - the lessee leases out the asset 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"]
["b.  Sale-leaseback - the sale of a property with an agreement that it will then be leased back.  Example:  A state agency sells a piece of property to a non-state company.  The sale includes a contractual agreement that the agency will lease office space on the property back from the company.  The State acts as both Seller and Lessee.","No"]
["c.  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 involved is the same entity on both sides of the transaction, 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."]
[]
["5.  Is the entire lease payment paid from One Source of funding or Divided between Funds or Agencies?","Divided - Fill out Gen Extra Info Tab"]
["If the payment is divided, then please switch to the \"Gen Extra Info\" Tab to fill out the payment 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 funding is all from One Source, please provide the following Chart of Accounts (COA) Elements below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["Agency *","see Gen Extra Info Tab"]
["Fund *"]
["Object *"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
["Dept Object"]
[]
["III.  Asset Information"]
["6.  Asset type that is being leased"]
["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) "]
[]
["Useful life of the leased asset"]
["7.  Useful life per Capital Asset conventions for the State (will automatically fill)",{"$f":"VLOOKUP(B65,VLookup!$A$15:$C$29,3,FALSE)","v":480},{"$f":"VLOOKUP(C65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(D65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(E65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(F65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(G65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(H65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(I65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(J65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(K65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(L65,VLookup!$A$15:$C$29,3,FALSE)","v":{"$error":"#N/A"}}]
[]
["8.  Total Remaining Lease Term per #18? (will automatically fill once completed below)",{"$f":"B112","v":96},1,{"$f":"D112","v":1},1,1,1,1,1,1,1,1]
[]
["Useful life for Leased Asset (will automatically fill) - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B68=\"Not Depreciable\",B70,IF(B68<B70,B68,B70))","v":96},{"$error":"#N/A"},{"$f":"IF(D68=\"Not Depreciable\",D70,IF(D68<D70,D68,D70))","v":{"$error":"#N/A"}},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"}]
["9.  Purchase option"]
["If the lease has a purchase option, is the agency reasonably certain it will be exercised? ","Yes"]
[]
["10.  Useful Life for Leased Asset based on answers above (will automatically fill)",{"$f":"IF(B74=\"Yes\",B68,B72)","v":480},{"$error":"#N/A"},{"$f":"IF(D74=\"Yes\",D68,D72)","v":{"$error":"#N/A"}},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"},{"$error":"#N/A"}]
[]
["11.  Additional considerations for measurement of Lease Asset:"]
["Per GASB 87, Lease Asset cost = \n  + Initial Lease Liability (calculated in PV calc tab)\n  + Lease payments made at or before start of lease (see (a) below)\n  - Lease incentives received at or before start of lease (see (b) below)\n  + Initial direct costs that are ancillary charges necessary to place the lease asset into service (see (c) below)"]
["a.  Enter the amount the State (Lessee) paid to the Lessor at or before the start of the lease.\n  - For example, a downpayment required to be paid (in addition to the first lease payment), or\n  - Other costs required to be reimbursed directly to the Lessor\n  - Do NOT include leasehold improvements paid for by the State to a contractor - that should be accounted for as a\n      leasehold improvement asset and is accounted for separately from this lease asset",25000]
["b.  Enter the amount the Lessor provided as incentives at or before the start of the lease.\n  - For example, paying a preexisting lease obligation to a third party in order to gain the lease, or\n  - Reimbursements of Lessee's costs (installation costs, moving costs, etc)",15000]
["c.  Enter any initial direct costs necessary to place the asset into service.\n  - For example, shipping and/or installation costs\n  - Do NOT include costs that would be considered debt issuance costs",0]
[]
["12.  If any amounts are entered in 11 (a), (b), or (c), please describe (indicate which question you are answering if more than one)","(a) Reimburse lessor for leasehold improvements above allowance; (b) Lessor reimbursed moving costs"]
[]
["13.  Please provide the COA Elements below where the payment (or incentive received from the Lessor) was posted.  REQUIRED\n       elements are starred (*):"]
["NOTE - if more than one, indicate with the corresponding letter (i.e. Fund:  (a) 1000, (b) AD1200)"]
["Agency *","(a & b) ADA"]
["Fund *","(a & b) 1000"]
["Object or Revenue Source (please indicate which one) *","(a) O - 7221, (b) O - 7599"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
["Dept Object"]
[]
["IV.  Lease Term"]
["14.  Lease commencement date"]
["Enter the lease start date - because this form is specifically for New leases entered into during FY2023, the dates should be between July 1, 2022 and June 30, 2023.","2022-12-01"]
[]
["15.  Contracted Lease End Date - Enter the date the contracted lease term ends.","2027-11-30"]
["Lease Term in months (will automatically fill) - HIDE THIS LINE IN FINAL VERSION",{"$f":"ROUND(DATEDIF(B102,B104,\"M\"),0)+1","v":60},1,{"$f":"ROUND(DATEDIF(D102,D104,\"M\"),0)+1","v":1},1,1,1,1,1,1,1,1]
[]
["16.  Options to extend term"]
["If lease extensions are available in the contract, AND the agency is reasonably certain to exercise it, enter # of months available to extend the lease.",36]
[]
["17.  Lease End Date including Extensions (will automatically fill).",{"$f":"EDATE(B102,B112)-1","v":"2030-11-30"},{"$f":"EDATE(C102,C112)-1","v":"1900-01-30"},{"$f":"EDATE(D102,D112)-1","v":"1900-01-30"},"1900-01-30","1900-01-30","1900-01-30","1900-01-30","1900-01-30","1900-01-30","1900-01-30","1900-01-30"]
[]
["18.  Total Lease Term including Extensions in Months (will automatically fill)",{"$f":"B105+B108","v":96},{"$f":"C105+C108","v":1},{"$f":"D105+D108","v":1},1,1,1,1,1,1,1,1]
[]
["19.  Frequency of lease payments"]
["Enter how often lease payments are made (Monthly, Quarterly, Semi-Annually, Annually).  If payments are Less Often than Annually, choose Annually from the dropdown list here (frequency must be at least Annually for Present Value Calculation to work).  There is a space in the PV Calc tab to describe the payment frequency if Less Often than Annually (Step #1f).","Quarterly"]
["# of pmts/yr - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B115=\"Weekly\",52,IF(B115=\"Monthly\",12,IF(B115=\"Quarterly\",4,IF(B115=\"Semi-Annually\",2,1))))","v":4},{"$f":"IF(C115=\"Weekly\",52,IF(C115=\"Monthly\",12,IF(C115=\"Quarterly\",4,IF(C115=\"Semi-Annually\",2,1))))","v":1},{"$f":"IF(D115=\"Weekly\",52,IF(D115=\"Monthly\",12,IF(D115=\"Quarterly\",4,IF(D115=\"Semi-Annually\",2,1))))","v":1},1,1,1,1,1,1,1,1]
[]
["20.  How many payments from lease start date (#14) to lease end date including extensions (#17)?"]
["-  If payments are monthly, this answer will most likely be the same as the calculated number of months.  Please calculate\n   carefully if payments are not monthly.\n- If payments are Less Often than Annually, list the # of years of the lease term rather than the total # of payments (e.g. payment\n  frequency every 5 years with a 20 year term would have a total of 4 payments, but enter 20 here instead of 4).  Again, this is\n  necessary for the Present Value Calculation to work.  If this is the case, read PV Calc instructions (Step #1g) carefully.",32]
[]
["21.  Was the first lease payment made at the beginning or at the end of the first payment period (a period is the length of time\n       between payments - see #19 frequency of payments)?"]
["- 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\n   payment period.\n- For a quarterly period, payment within first 45 days would be beginning of the period.","Begin"]
["Coding for Formulas - Begin = 1, End = 0 - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B122=\"Begin\",1,0)","v":1},{"$f":"IF(C122=\"Begin\",1,0)","v":0},{"$f":"IF(D122=\"Begin\",1,0)","v":0},0,0,0,0,0,0,0,0]
[]
["22.  When is the first payment due (most likely would be within FY2023)?","2022-12-01"]
[]
["V.  Lease Liability"]
["Lease payment - PLEASE SEPARATE PAYMENTS TO BE MADE BETWEEN #23, #24 & #25 as much as possible:"]
["23.  Fixed Lease payment - Enter FIRST Fixed Lease Payment Amount here"]
["To calculate the Fixed Lease Payment:\n(a) Start with the fixed lease periodic payments stated in the contract.  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 fixed lease\n       payment here but detailed below.\n(b) In addition, add \"Fixed In-Substance Fees\" to the above amount (see definition below under variable payments).\n(c) 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 published as of the start of the lease.",56000]
["Because payments may not remain the same throughout the lease term, fill out the Payment Schedule on the PV Calc tab.\nA space is provided (Step #1f) to describe how often payments increase, formulas, appraisal requirements, etc."]
[]
["24.  Variable Usage Fees over and above Fixed In-Substance Fees (do NOT include variable fees on PV Calc tab)"]
["\"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 the fixed lease portion above, and the variable answer here would be \"$.01/copy over 50,000 copies\" ($500 is equal to $.01/copy * 50,000).  Per GASB 87, also include how the fee is determined.","Shared Space fees in excess of $1,200/qtr will be billed annually - amt determined based on total shared space expenses for the prior year divided pro rata between tenants based on sq. footage less the $4.8K already paid\n(the $1,200/qtr fee is included in the fixed lease payment)"]
[]
["25.  Utilities, Taxes, Service costs (janitorial or maintenance agreements), or Other additional charges\n       (do NOT include as part of Fixed Lease Payments on PV Calc tab)"]
["a.  Enter the amount for any additional charges identified, if paid to the lessor and is separately identified in the lease agreement.",{"$f":"B130*0.025+1000","v":2400}]
[]
["b.  Please briefly describe the additional charges.","2.5% occupancy tax + $1,000/qtr for janitorial svcs"]
[]
["26.  Residual Value Guarantee:"]
["a.  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"]
[]
["b.  If so, then how much is the maximum residual value guarantee (or penalty amount) listed in the contract?",0]
[]
["c.  Does the agency believe the leased asset will meet that minimum value or condition to avoid the Residual Value Guarantee fee/penalty at the end of the lease term?","N/A"]
["If No, then enter the amount expected to be owed on the PV Calc tab."]
[]
["27.  Purchase Option:"]
["Is the agency reasonably certain to exercise that option (will automatically fill from response on #9)?",{"$f":"IF(B74=\"Yes\",\"Yes - Fill out on PV Calc Tab\",\"No\")","v":"Yes - Fill out on PV Calc Tab"},"No",{"$f":"IF(D74=\"Yes\",\"Yes - Fill out on PV Calc Tab\",\"No\")","v":"No"},"No","No","No","No","No","No","No","No"]
["If Yes, then enter the Purchase Price in the PV Calc tab."]
[]
["28.  Termination penalties:"]
["a.  Is there a termination penalty if the lease is terminated before the end of the lease term?","No"]
[]
["b.  If so, is the agency reasonably certain to terminate the lease early?"]
["Answer Yes only if the agency believes that a fiscal funding clause or termination clause will be exercised and there will be a penalty assessed if it is.","N/A"]
["If Yes, then enter the termination penalty on the PV Calc tab."]
[]
["29.  Lease incentives (during lease term):"]
["a.  Does the lease agreement contain lease incentives that will occur AFTER the start of the lease term?\n  - For example, Abated or Free Rent periods,\n  -  Reductions in rent,\n  - Bonus amounts paid from the lessor to the lessee, etc?","Yes - Fill out on PV Calc Tab"]
[]
["b.  If Yes, please describe:","Free Rent Quarter for\n12/2023-2/2024"]
["Read instructions on PV Calc tab carefully on how to include lease incentives in the Payment Schedule (#1d & 1e)."]
[]
["VI.  Interest Rate"]
["30.  Enter the interest rate for lease (with four digits past the decimal) using one of the three methods listed below:"]
["a.  If the interest rate is specifically identified in the contract (explicit rate), use this rate."]
["b.  If the explicit rate is unknown, there may be an implicit rate (please see tab, \"Implicit Rate info\" for additional information\n      and a place to enter the information to calculate it)."]
["c.  If both the explicit interest rate and implicit rate are unknown, identify the interest rate in the table below using the lease\n      term (total lease term in months is copied here from #18 above for ease of reference).",{"$f":"B112","v":96},1,{"$f":"D112","v":1},1,1,1,1,1,1,1,1]
["For example: If initial contract term was 5 years (60 months) plus a 4-year (48 month) extension,  for a total of 9 years (108 months) use the Long-Term rate below.",1.8606000000000001E-2]
[]
[]
[]
[]
[]
["The interest rates have been updated for"]
["FY2023 & will be reassessed each year."]
["Short-Term in months = 0 - 48 months"]
["Mid-Term in months = 49 - 96 months"]
["Long-Term in months = 97 months or longer"]
[]
["31.  Periodic Interest Rate - based on frequency of payments (will automatically fill)",{"$f":"ROUND(B172/B116,6)","v":4.6519999999999999E-3},0,{"$f":"ROUND(D172/D116,6)","v":0},0,0,0,0,0,0,0,0]
[]
["VII.  Calculate the Lease Liability & Lease Asset"]
["32.  Fill out the Lease Payment Schedule on the \"PV Calc\" tab corresponding to the correct Lease #."]
["The Present Value of the Lease Liability, Amortization Table and Lease Asset Cost will be calculated in each respective PV Calc tab.  These are calculated with macros, and are not required to be completed by the agencies.\n  - At a minimum, the Lease Payment Schedule must be completed.","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":131,"n_cols":12,"rows_kept":131,"cut":false,"hidden":false,"merged":[[0,0,0,1],[2,0,2,1],[4,0,4,1],[5,0,5,1],[6,0,6,1]]}
["GASB 87 Lease Collection Form - General Extra Info - for Leases paid from multiple sources"]
[]
["If a lease has more than one funding source for the payments during the lease term, please complete the information below. "]
[]
["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 - If the Agency divides out the lease payment with a Cost Allocation Function (or divides it out to more than the 4 lines provided), then ONLY provide the breakout for the 3 REQUIRED COA Elements to reduce the number of lines needed to be reported.  The other COA elements may be important for agency or unit internal reporting, but is not necessary for reporting at the ACFR level."]
["Note 3 - When payments are split between multiple COAs, please ONLY provide split information for the Fixed amount to be paid.  Please exclude any variable payments or payments for services, maintenance, taxes, etc that are identified separately within the lease contract."]
[]
["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"]
["Contract # from Data Input tab (will fill automatically)",{"$f":"'Data Input'!B35","v":"KR12-3456"},{"$f":"'Data Input'!C35","v":0},{"$f":"'Data Input'!D35","v":0},{"$f":"'Data Input'!E35","v":0},{"$f":"'Data Input'!F35","v":0},{"$f":"'Data Input'!G35","v":0},{"$f":"'Data Input'!H35","v":0},{"$f":"'Data Input'!I35","v":0},{"$f":"'Data Input'!J35","v":0},{"$f":"'Data Input'!K35","v":0},{"$f":"'Data Input'!L35","v":0}]
[]
["Is the payment divided by a Percentage (%), or a Specific Dollar ($) Amount (i.e. 50% paid by one Source, and 50% paid by another Source; or first $1,000 paid by a Grant, the remainder paid by another Source)?","Percentage (%)"]
["Based on your answer, please fill out the proper section below (Percentage or Dollar Amount Split)"]
[]
["Percentage (%) Split:"]
["How many Sources are there (how many funding lines in your lease payment)?",2]
[]
["1 - Percentage of funding from First Source",0.7]
["Please provide COA for First Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["1a - Agency *","ADA"]
["1b - Fund *","AD1234"]
["1c - Object *",7221]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
["1k - Dept Object"]
[]
["2 - Percentage of funding from Second Source",0.3]
["Please provide COA for Second Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["2a - Agency *","DCA"]
["2b - Fund *","DC4567"]
["2c - Object *",7221]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
["2k - Dept Object"]
[]
["3 - Percentage of funding from Third Source"]
["Please provide COA for Third Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Object *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
["3k - Dept Object"]
[]
["4 - Percentage of funding from Fourth Source"]
["Please provide COA for Fourth Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Object *"]
["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 Sources are there (how many funding lines in your lease payment)?"]
[]
["1 - Amount of funding from First Source"]
["Please provide COA for First Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["1a - Agency *"]
["1b - Fund *"]
["1c - Object *"]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
["1k - Dept Object"]
[]
["2 - Amount of funding from Second Source"]
["Please provide COA for Second Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["2a - Agency *"]
["2b - Fund *"]
["2c - Object *"]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
["2k - Dept Object"]
[]
["3 - Amount of funding from Third Source"]
["Please provide COA for Third Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Object *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
["3k - Dept Object"]
[]
["4 - Amount of funding from Fourth Source"]
["Please provide COA for Fourth Funding Source below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments made for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Object *"]
["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":29,"n_cols":12,"rows_kept":29,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["GASB 87 Lease Collection Form - 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"]
["Contract # from Data Input tab (will fill automatically)",{"$f":"'Data Input'!B35","v":"KR12-3456"},{"$f":"'Data Input'!C35","v":0},{"$f":"'Data Input'!D35","v":0},{"$f":"'Data Input'!E35","v":0},{"$f":"'Data Input'!F35","v":0},{"$f":"'Data Input'!G35","v":0},{"$f":"'Data Input'!H35","v":0},{"$f":"'Data Input'!I35","v":0},{"$f":"'Data Input'!J35","v":0},{"$f":"'Data Input'!K35","v":0},{"$f":"'Data Input'!L35","v":0}]
["Cash Flows at Beginning or End (Section IV, #21 of Data Input tab)",{"$f":"'Data Input'!B122","v":"Begin"},{"$f":"'Data Input'!C122","v":0},{"$f":"'Data Input'!D122","v":0},{"$f":"'Data Input'!E122","v":0},{"$f":"'Data Input'!F122","v":0},{"$f":"'Data Input'!G122","v":0},{"$f":"'Data Input'!H122","v":0},{"$f":"'Data Input'!I122","v":0},{"$f":"'Data Input'!J122","v":0},{"$f":"'Data Input'!K122","v":0},{"$f":"'Data Input'!L122","v":0}]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!B119","v":32},{"$f":"'Data Input'!C119","v":0},{"$f":"'Data Input'!D119","v":0},{"$f":"'Data Input'!E119","v":0},{"$f":"'Data Input'!F119","v":0},{"$f":"'Data Input'!G119","v":0},{"$f":"'Data Input'!H119","v":0},{"$f":"'Data Input'!I119","v":0},{"$f":"'Data Input'!J119","v":0},{"$f":"'Data Input'!K119","v":0},{"$f":"'Data Input'!L119","v":0}]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!B115","v":"Quarterly"},{"$f":"'Data Input'!C115","v":0},{"$f":"'Data Input'!D115","v":0},{"$f":"'Data Input'!E115","v":0},{"$f":"'Data Input'!F115","v":0},{"$f":"'Data Input'!G115","v":0},{"$f":"'Data Input'!H115","v":0},{"$f":"'Data Input'!I115","v":0},{"$f":"'Data Input'!J115","v":0},{"$f":"'Data Input'!K115","v":0},{"$f":"'Data Input'!L115","v":0}]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!B125","v":"2022-12-01"},{"$f":"'Data Input'!C125","v":"00:00:00"},{"$f":"'Data Input'!D125","v":"00:00:00"},{"$f":"'Data Input'!E125","v":"00:00:00"},{"$f":"'Data Input'!F125","v":"00:00:00"},{"$f":"'Data Input'!G125","v":"00:00:00"},{"$f":"'Data Input'!H125","v":"00:00:00"},{"$f":"'Data Input'!I125","v":"00:00:00"},{"$f":"'Data Input'!J125","v":"00:00:00"},{"$f":"'Data Input'!K125","v":"00:00:00"},{"$f":"'Data Input'!L125","v":"00:00:00"}]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!B102","v":"2022-12-01"},{"$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"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!B110","v":"2030-11-30"},{"$f":"'Data Input'!C110","v":"1900-01-30"},{"$f":"'Data Input'!D110","v":"1900-01-30"},{"$f":"'Data Input'!E110","v":"1900-01-30"},{"$f":"'Data Input'!F110","v":"1900-01-30"},{"$f":"'Data Input'!G110","v":"1900-01-30"},{"$f":"'Data Input'!H110","v":"1900-01-30"},{"$f":"'Data Input'!I110","v":"1900-01-30"},{"$f":"'Data Input'!J110","v":"1900-01-30"},{"$f":"'Data Input'!K110","v":"1900-01-30"},{"$f":"'Data Input'!L110","v":"1900-01-30"}]
[]
["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 (Sec VI, #30) 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":135,"n_cols":16,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Example (column B)","Contract #",{"$f":"'Data Input'!B35","v":"KR12-3456"},null,"Present Value Table",null,null,null,null,"Amortization Table"]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!B184","v":4.6519999999999999E-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 (Section IV, #21)",{"$f":"'Data Input'!B122","v":"Begin"},"Important:",null,1,56000,{"$f":"F3 / (((1+$B$2)) ^ (E3-1))","v":56000},null,null,1,{"$f":"B6","v":"2022-12-01"},{"$f":"B25","v":2524229.8473974126},56000,{"$f":"M3-O3","v":56000},0,{"$f":"L3-N3","v":2468229.8473974126}]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!B119","v":32},"Do not insert/delete/move",null,2,56000,55740.694290162159,null,null,2,{"$f":"EDATE($B$6,3)","v":"2023-03-01"},{"$f":"P3","v":2468229.8473974126},56000,44517.79,{"$f":"ROUND(L4*$B$2,2)","v":11482.21},2423712.0573974126]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!B115","v":"Quarterly"},"cells B2:B8",null,3,56000,55482.589284809226,null,null,3,{"$f":"EDATE($B$6,6)","v":"2023-06-01"},2423712.0573974126,56000,44724.89,11275.11,2378987.1673974125]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!B125","v":"2022-12-01"},"(macros depend on them)",null,4,56000,55225.679424128175,null,null,4,{"$f":"EDATE($B$6,9)","v":"2023-09-01"},2378987.1673974125,56000,44932.95,11067.05,2334054.2173974123]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!B102","v":"2022-12-01"},null,null,5,56000,54969.959174050491,null,null,5,{"$f":"EDATE($B$6,12)","v":"2023-12-01"},2334054.2173974123,56000,45141.979999999996,10858.02,2288912.2373974123]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!B110","v":"2030-11-30"},null,null,6,56000,54715.423026132914,null,null,6,{"$f":"EDATE($B$6,15)","v":"2024-03-01"},2288912.2373974123,56000,45351.979999999996,10648.02,2243560.2573974123]
[null,null,null,null,7,56000,54462.065497438831,null,null,7,{"$f":"EDATE($B$6,18)","v":"2024-06-01"},2243560.2573974123,56000,45562.96,10437.040000000001,2197997.2973974124]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments",null,8,56000,54209.881130420115,null,null,8,{"$f":"EDATE($B$6,21)","v":"2024-09-01"},2197997.2973974124,56000,45774.92,10225.08,2152222.3773974124]
["Payment Amount #1 & # of Payments at this amount",56000,8,null,9,0,0,null,null,9,{"$f":"EDATE($B$6,24)","v":"2024-12-01"},2152222.3773974124,0,-10012.14,10012.14,2162234.5173974126]
["Payment Amount #2 & # of Payments at this amount",0,1,null,10,56000,53709.010177454089,null,null,10,{"$f":"EDATE($B$6,27)","v":"2025-03-01"},2162234.5173974126,56000,45941.29,10058.709999999999,2116293.2273974125]
["Payment Amount #3 & # of Payments at this amount",56000,7,null,11,56000,53460.312802297798,null,null,11,{"$f":"EDATE($B$6,30)","v":"2025-06-01"},2116293.2273974125,56000,46155,9845,2070138.2273974125]
["Payment Amount #4 & # of Payments at this amount",59000,16,null,12,56000,53212.767010166506,null,null,12,{"$f":"EDATE($B$6,33)","v":"2025-09-01"},2070138.2273974125,56000,46369.72,9630.2800000000007,2023768.5073974126]
["Totals from add'l table below (if needed)",{"$f":"B88","v":0},{"$f":"C88","v":0},null,13,56000,52966.367468702098,null,null,13,{"$f":"EDATE($B$6,36)","v":"2025-12-01"},2023768.5073974126,56000,46585.43,9414.57,1977183.0773974126]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":1784000},{"$f":"SUM(C11:C15)","v":32},null,14,56000,52721.108870237746,null,null,14,{"$f":"EDATE($B$6,39)","v":"2026-03-01"},1977183.0773974126,56000,46802.14,9197.86,1930380.9373974127]
[null,null,null,null,15,56000,52476.985931683557,null,null,15,{"$f":"EDATE($B$6,42)","v":"2026-06-01"},1930380.9373974127,56000,47019.87,8980.1299999999992,1883361.0673974126]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab",null,16,56000,52233.993394412741,null,null,16,{"$f":"EDATE($B$6,45)","v":"2026-09-01"},1883361.0673974126,56000,47238.6,8761.4,1836122.4673974125]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!B147","v":"N/A"},null,17,59000,54777.418489727788,null,null,17,{"$f":"EDATE($B$6,48)","v":"2026-12-01"},1836122.4673974125,59000,50458.36,8541.64,1785664.1073974124]
["Purchase Option (Section III, #9 & Section V, #27)",1000000,{"$f":"'Data Input'!B151","v":"Yes - Fill out on PV Calc Tab"},null,18,59000,54523.773893574864,null,null,18,{"$f":"EDATE($B$6,51)","v":"2027-03-01"},1785664.1073974124,59000,50693.09,8306.91,1734971.0173974123]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!B158","v":"N/A"},null,19,59000,54271.303788351455,null,null,19,{"$f":"EDATE($B$6,54)","v":"2027-06-01"},1734971.0173974123,59000,50928.91,8071.09,1684042.1073974124]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":1000000},null,null,20,59000,54020.002735625319,null,null,20,{"$f":"EDATE($B$6,57)","v":"2027-09-01"},1684042.1073974124,59000,51165.84,7834.16,1632876.2673974123]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","v":2784000},null,null,21,59000,53769.865322146688,null,null,21,{"$f":"EDATE($B$6,60)","v":"2027-12-01"},1632876.2673974123,59000,51403.86,7596.14,1581472.4073974122]
[null,null,"Important:",null,22,59000,53520.886159731614,null,null,22,{"$f":"EDATE($B$6,63)","v":"2028-03-01"},1581472.4073974122,59000,51642.99,7357.01,1529829.4173974122]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":2524229.8473974126},"Do not insert/delete/move",null,23,59000,53273.059885145929,null,null,23,{"$f":"EDATE($B$6,66)","v":"2028-06-01"},1529829.4173974122,59000,51883.229999999996,7116.77,1477946.1873974123]
[null,null,"NPV must be cell B25",null,24,59000,53026.381159989643,null,null,24,{"$f":"EDATE($B$6,69)","v":"2028-09-01"},1477946.1873974123,59000,52124.59,6875.41,1425821.5973974122]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!B80-'Data Input'!B81+'Data Input'!B82","v":10000},"(macros depend on it)",null,25,59000,52780.844670582104,null,null,25,{"$f":"EDATE($B$6,72)","v":"2028-12-01"},1425821.5973974122,59000,52367.08,6632.92,1373454.5173974121]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":2534229.8473974126},null,null,26,59000,52536.445127847343,null,null,26,{"$f":"EDATE($B$6,75)","v":"2029-03-01"},1373454.5173974121,59000,52610.69,6389.31,1320843.8273974122]
[null,null,null,null,27,59000,52293.177267200335,null,null,27,{"$f":"EDATE($B$6,78)","v":"2029-06-01"},1320843.8273974122,59000,52855.43,6144.57,1267988.3973974122]
["The Agency is not required to calculate the Present Value and/or enable Macros,",null,null,null,28,59000,52051.035848433414,null,null,28,{"$f":"EDATE($B$6,81)","v":"2029-09-01"},1267988.3973974122,59000,53101.32,5898.68,1214887.0773974122]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed.",null,null,null,29,59000,51810.015655603544,null,null,29,{"$f":"EDATE($B$6,84)","v":"2029-12-01"},1214887.0773974122,59000,53348.35,5651.65,1161538.7273974121]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:",null,null,null,30,59000,51570.111496919868,null,null,30,{"$f":"EDATE($B$6,87)","v":"2030-03-01"},1161538.7273974121,59000,53596.520000000004,5403.48,1107942.207397412]
[null,null,null,null,31,59000,51331.31820463192,null,null,31,{"$f":"EDATE($B$6,90)","v":"2030-06-01"},1107942.207397412,59000,53845.85,5154.1499999999996,1054096.3573974119]
["Note 1:  First make sure your Excel settings are correct -","See How to Enable Macros tab",null,null,32,1059000,917087.37020980439,null,null,32,{"$f":"EDATE($B$6,93)","v":"2030-09-01"},1054096.3573974119,1059000,1054096.3600000001,{"$f":"ROUND(L34*$B$2,2)-0.02","v":4903.6399999999994},-2.602588152512908E-3]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!B164","v":"Free Rent Quarter for\n12/2023-2/2024"}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
["Fixed + Fixed-In-Substance payment for 1st 4 yrs = $56,000; Payment for year 5 = $59,000; extension period amount to be negotiated - therefore, using most recent payment amount before extension period of $59,000."]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 1 (column C)","Contract #",{"$f":"'Data Input'!C35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!C184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!C122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!C119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!C115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!C125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!C102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!C110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!C147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!C151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!C158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!C80-'Data Input'!C81+'Data Input'!C82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!C164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 2 (column D)","Contract #",{"$f":"'Data Input'!D35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!D184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!D122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!D119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!D115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!D125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!D102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!D110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!D147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!D151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!D158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!D80-'Data Input'!D81+'Data Input'!D82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!D164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 3 (column E)","Contract #",{"$f":"'Data Input'!E35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!E184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!E122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!E119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!E115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!E125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!E102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!E110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!E147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!E151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!E158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!E80-'Data Input'!E81+'Data Input'!E82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!E164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 4 (column F)","Contract #",{"$f":"'Data Input'!F35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!F184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!F122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!F119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!F115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!F125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!F102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!F110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!F147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!F151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!F158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!F80-'Data Input'!F81+'Data Input'!F82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!F164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 5 (column G)","Contract #",{"$f":"'Data Input'!G35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!G184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!G122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!G119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!G115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!G125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!G102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!G110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!G147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!G151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!G158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!G80-'Data Input'!G81+'Data Input'!G82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!G164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 6 (column H)","Contract #",{"$f":"'Data Input'!H35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!H184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!H122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!H119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!H115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!H125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!H102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!H110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!H147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!H151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!H158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!H80-'Data Input'!H81+'Data Input'!H82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!H164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 7 (column I)","Contract #",{"$f":"'Data Input'!I35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!I184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!I122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!I119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!I115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!I125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!I102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!I110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!I147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!I151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!I158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!I80-'Data Input'!I81+'Data Input'!I82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!I164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 8 (column J)","Contract #",{"$f":"'Data Input'!J35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!J184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!J122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!J119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!J115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!J125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!J102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!J110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!J147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!J151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!J158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!J80-'Data Input'!J81+'Data Input'!J82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!J164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 9 (column K)","Contract #",{"$f":"'Data Input'!K35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!K184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!K122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!K119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!K115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!K125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!K102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!K110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!K147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!K151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!K158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!K80-'Data Input'!K81+'Data Input'!K82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!K164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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":135,"n_cols":3,"rows_kept":135,"cut":false,"hidden":false,"merged":[[46,2,49,2],[55,0,57,2]]}
["Present Value Calculations tab - Lease 10 (column L)","Contract #",{"$f":"'Data Input'!L35","v":0}]
["Periodic Interest Rate (Section VI, #30-31 from Data Input tab)",{"$f":"'Data Input'!L184","v":0}]
["Cash Flows at Beginning or End (Section IV, #21)",{"$f":"'Data Input'!L122","v":0},"Important:"]
["Number of payments (Section IV, #20)",{"$f":"'Data Input'!L119","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section IV, #19)",{"$f":"'Data Input'!L115","v":0},"cells B2:B8"]
["Date of first payment (Section IV, #22)",{"$f":"'Data Input'!L125","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section IV, #14)",{"$f":"'Data Input'!L102","v":"00:00:00"}]
["Lease End Date including Extensions (Section IV, #17)",{"$f":"'Data Input'!L110","v":"1900-01-30"}]
[]
["Fixed Lease Payment Schedule (Section V, #23 & see instructions below):","Amount","# of Payments"]
["Payment Amount #1 & # of Payments 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":"B88","v":0},{"$f":"C88","v":0}]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":0},{"$f":"SUM(C11:C15)","v":0}]
[]
["Other Payment Considerations (manually fill blue cells):",null,"Answers per Data Input Tab"]
["Expected Residual Value Guarantee (Section V, #26)",0,{"$f":"'Data Input'!L147","v":0}]
["Purchase Option (Section III, #9 & Section V, #27)",0,{"$f":"'Data Input'!L151","v":"No"}]
["Termination Penalty (Section V, #28)",0,{"$f":"'Data Input'!L158","v":0}]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0}]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","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"]
["Additions/Subtractions for Lease Asset Cost (Section III, #11-13)",{"$f":"'Data Input'!L80-'Data Input'!L81+'Data Input'!L82","v":0},"(macros depend on it)"]
["Lease Asset Cost (calculated after Macros below run)",{"$f":"B25+B27","v":0}]
[]
["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 \"Fixed Lease Payment Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list the beginning payment amount (col B) & # of payments to be paid at that amount (col C), 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 index or rate."]
["d - If there is an Abated or Free Rent period (skipped payment) indicated in the lease contract,",null,"Desc of Lease Incentives"]
["indicate that with a $0 payment amount (col B) & the number of payments to be skipped (col C).",null,{"$f":"'Data Input'!L164","v":0}]
["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 (col B) and 1 (col C), then line #3 as the continuing payments after the Free Rent period."]
["e - If a Bonus payment from the lessor is included in the contract, subtract that amount from the payment closest to the proper"]
["chronological period (as described in d above).  This may cause that payment amount to be negative."]
["f - Use the blue box below to describe how payments change over time.  For example - (a) Payment increases 5% every 10 yrs;"]
["(b) Appraisal to be completed every 25 years; or (c) Payment recalculated every 5 yrs based on CPI, etc."]
["Also describe payment frequency if Less Often than Annually."]
[]
[]
[]
["g - If payments are Less Often than Annually,indicate this in the Payment Schedule above by listing first payment on the first row (1 in"]
["col C), then the 2nd row should show how many years to be skipped in col C ($0 in col B), then the 3rd row will show the 2nd payment."]
["Repeat until all payments & all years are represented in the Payment Schdl (total in col C should tie to total yrs of lease term & cell B4)."]
["h - If there are not enough lines in the table above, use the table below to input the additional payment amounts, and the totals from"]
["the additional table will automatically be added to the Payment Schedule above."]
["i - Verify that the total # of payments in cell B4 is the same as the sum of col C (cell C16)."]
[]
["Additional Lease Payment 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 Payments (will automatically be added to the Schedule above)",{"$f":"(B67*C67)+(B68*C68)+(B69*C69)+(B70*C70)+(B71*C71)+(B72*C72)+(B73*C73)+(B74*C74)+(B75*C75)+(B76*C76)+(B77*C77)+(B78*C78)+(B79*C79)+(B80*C80)+(B81*C81)+(B82*C82)+(B83*C83)+(B84*C84)+(B85*C85)+(B86*C86)+(B87*C87)","v":0},{"$f":"SUM(C67:C87)","v":0}]
[]
[]
["Step 2 - Fill out the \"Other Payment Considerations\" section (starting on row 18) ONLY if instructed to do so on the Data Input tab"]
["The Residual Value Guarantee, Purchase Option & Termination Penalty lines should only have amounts listed if they are deemed"]
["Reasonably Certain to be exercised based on decisions by the Agency (responses from the Data Input tab are shown in col C)."]
[]
["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 Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into col 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  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 Purchase Option listed for $500,000, then the final payment in the Table should be $520,000."]
["d -  Check to make sure that the sum of all the payments entered into col F of the Table is the same as the Total Undiscounted Cash"]
["Flows in cell B23."]
[]
["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 B8.  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 + rows), then check the dates the amounts change in the"]
["Amort Table (col 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 1i & check for Table payments in 4d."]
["ii - Double-check cells B2-B8 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-B8) - the corresponding question #s 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 (1i, 4d, 6a, 6b, and 6di-6diii) are correct."]
[]
["Step 8 - See Net Present Value calculated in cell B25 for the Lease Liability and the Asset Cost in cell B28."]
["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,"Not Depreciable"]
["Leased Land ",null,"Not Depreciable"]

{"sheet":"Drop Down List","index":16,"n_rows":48,"n_cols":1,"rows_kept":48,"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 / Residual Value?"]
["Yes"]
["No - Fill out on PV Calc Tab"]
["N/A"]
[]
["Purchase Option / Termination Penalty?"]
["Yes - Fill out on PV Calc Tab"]
["No"]
["N/A"]
[]
["One source / divided"]
["One Source"]
["Divided - Fill out Gen Extra Info Tab"]
[]
["Type of Division"]
["Specific $ amount"]
["Percentage (%)"]
[]
["Planned Cancellation"]
["Yes - use Planned End Date for Lease End Date"]
["No"]