{"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":140,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Data Input","index":0,"n_rows":175,"n_cols":12,"rows_kept":175,"cut":false,"hidden":false,"merged":[[0,0,0,1]]}
["STATE OF ARIZONA\nCLOSING PACKAGE\nN - GASB 87 LEASES - Form 72a\nCollection Form for FY2024 Receivable Leases\n(State as LESSOR)"]
[" ----- Due Date 08/09/2024 -----"]
["This FILLABLE form is designed to capture your agency's GASB 87 lease information for NEW leases entered into during FY2024."]
[]
["Reporting background:   "]
["Please complete this template for every lease that qualifies as a GASB 87 Lease AND has a start date during FY2024\n(July 1, 2023 - June 30, 2024).\nFor example:\n  - Receivable leases where the State is the LESSOR,\n   - Meets scope requirements and is not listed as specifically EXCLUDED,\n   - Total payments to be received 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 72 - \"Decision Form for Lessor\" 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 FY2024 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 72a - Collection Form for Lessor - FY2024\")."]
["3.  Complete the following sections by entering in the applicable information for each lease within a separate column\n     (starting with column C).  There are 10 columns provided.  If you have more than 10 GASB 87 Receivable Leases,\n     duplicate the 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)."]
["5.  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."]
[]
["For details on GASB 87, see the GASB 87 Statement on the GASB website, link at right:","GASB Statement 87"]
[]
["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 GASB 87 Receivable Leases were entered into during FY2024?\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 # (If unknown or unassigned, please create your own.)","ABC 123"]
[]
["2.  Name of the party you are leasing to (Lessee):","ABC Land Development"]
[]
["3.  Address of the Leased Property or Description of Leased Asset"]
["If leasing out office space, land or right-of-way, enter the full address of the leased property.  For equipment or something else, provide a description of the leased asset (for example:  HP 4700 Copier or 2021 Ford F150 truck, VIN #ABC123).","1234 Beaver St, Suite 300, Mayberry, AZ  86123"]
[]
["4.  Is the lease any of the following (for disclosure only):"]
["a.  Sublease - initially the State agency leases an asset from a third party, but then leases the asset out to another party.\n     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\n     Lessee and Lessor.\n     Since the other parties involved are 2 different entities, both sides of the lease must be reported separately and should NOT\n     be netted together.","No"]
["b.  Sale-leaseback - the sale of a property with an agreement that the property will then be leased back.\n     Example:  A non-state entity sells a piece of property to a state agency with the contractual agreement that the non-state\n     entity will lease the asset back. The state agency is the Purchasor and the Lessor.","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\n     same party.\n     Example: A state agency owns a piece of land and leases it to a non-state company. The agreement obligates the company to\n     build an office and lease it back to the state agency.  The State acts as both Lessee and Lessor.\n     In this case, since the other party is the same entity, the lease proceeds and payouts will be netted for reporting purposes.","No"]
["If any of the 3 questions above are answered Yes, you will be contacted by the GAAP Group for additional info."]
[]
["5.  Is the entire lease payment received recorded 100% into a single Chart of Accounts string (COA) or Divided between Funds or\n     Agencies?","Divided - Fill out Gen Extra Info Tab"]
["If the receipt of payment is divided, then please switch to the \"Gen Extra Info\" Tab to fill out the deposit split info (click on hyperlink to the right for correct corresponding Lease # column).","Example - Gen Extra Info","Lease # 1 - Gen Extra Info","Lease # 2 - Gen Extra Info","Lease # 3 - Gen Extra Info","Lease # 4 - Gen Extra Info","Lease # 5 - Gen Extra Info","Lease # 6 - Gen Extra Info","Lease # 7 - Gen Extra Info","Lease # 8 - Gen Extra Info","Lease # 9 - Gen Extra Info","Lease # 10 - Gen Extra Info"]
["If the receipt of payment is recorded into a single COA, please provide the following COA Elements below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the receipt of payments for each lease."]
["Agency *","see Gen Extra Info Tab"]
["Fund *"]
["Revenue Source *"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
[]
["III.  Asset Information"]
["6.  Asset type that is being leased out"]
["Using the drop down list, select the Asset Type that is being leased. If the leased asset type is not listed, please choose a description that is closest to your leased asset (or contact the GAAP Group for further assistance).","Leased Constructed Buildings (Office Space) "]
[]
["7.  Return of the Asset"]
["Does the contract require the lessee to return the asset in its original or enhanced condition?","No"]
["If above question is answered Yes, then GASB 87 requires that the asset accumulate NO DEPRECIATION during the lease period as it is expected to be returned in the original or enhanced condition.  If, however, there is a residual value guarantee set at a value lower than its original value at commencement of the lease, depreciation should still be calculated.  If the asset is Land, then there should not be any depreciation regardless."]
[]
["8.  What is the asset number listed on the AFIS Fixed Asset Module (if not in AFIS, please explain)?","AD1000"]
[]
["IV.  Deferred Inflow of Resources"]
["9.  Measurement of Deferred Inflow of Resources"]
["Per GASB 87, Deferred Inflow of Resources = \n  + Initial Lease Receivable (calculated in PV Calc tab)\n  + Lease payments received from the lessee at or before start of lease that relate to future periods (see (a) below)\n  - Lease incentives paid to the lessee at or before start of lease (see (b) below)"]
["a.  Enter the amount the Lessee paid to the State (Lessor) at or before the start of the lease that relates to future periods.\n  - For example, the final month's rent\n  - Do NOT include downpayments (above first required payment) as those are not related to a future period",302000]
["b.  Enter the amount the State (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]
[]
["10.  If any amounts are entered in 9(a) or (b), please describe (indicate which question you are answering if more than one)","(a) Last month's rent, (b) Reimbursed moving costs to Lessee"]
[]
["11.  If any amounts are entered in 9(a) or (b), provide the COA Elements below where the payment from the lessee (or incentive\n        paid by the State (Lessor)) was posted.  REQUIRED 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) R - 4632, (b) O - 7599"]
["Appropriation"]
["Unit"]
["Task"]
["Sub Task"]
["Program"]
["Phase"]
["Program Period"]
["Dept Object"]
[]
["V.  Lease Term"]
["12.  Lease commencement date"]
["Lease Start Date - because this form is specifically for New leases entered into during FY2024, the dates should be between\nJuly 1, 2023 and June 30, 2024.","2023-10-01"]
[]
["13.  Contracted lease term"]
["Lease End Date - Enter the date the contracted lease term ends.","2033-09-30"]
["Lease Term in months (will automatically fill) - HIDE THIS LINE IN FINAL VERSION",{"$f":"ROUND(DATEDIF((B96),B99,\"M\"),0)+1","v":120},{"$f":"ROUND(DATEDIF((C96),C99,\"M\"),0)+1","v":1},{"$f":"ROUND(DATEDIF((D96),D99,\"M\"),0)+1","v":1},1,1,1,1,1,1,1,1]
[]
["14.  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.",60]
[]
["15.  Lease End Date including Extensions (will automatically fill).",{"$f":"EDATE(B96,B107)-1","v":"2038-09-30"},"1900-01-30",{"$f":"EDATE(D96,D107)-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"]
[]
["16.  Total Lease Term including Extensions in Months (will automatically fill).",{"$f":"B100+B103","v":180},1,{"$f":"D100+D103","v":1},1,1,1,1,1,1,1,1]
[]
["17.  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 (#1f).","Semi-annually"]
["# of pmts/yr - HIDE THIS LINE IN FINAL VERSION",{"$f":"IF(B110=\"Weekly\",52,IF(B110=\"Monthly\",12,IF(B110=\"Quarterly\",4,IF(B110=\"Semi-Annually\",2,1))))","v":2},{"$f":"IF(C110=\"Weekly\",52,IF(C110=\"Monthly\",12,IF(C110=\"Quarterly\",4,IF(C110=\"Semi-Annually\",2,1))))","v":1},1,{"$f":"IF(E110=\"Weekly\",52,IF(E110=\"Monthly\",12,IF(E110=\"Quarterly\",4,IF(E110=\"Semi-Annually\",2,1))))","v":1},1,1,1,1,1,1,1]
[]
["18.  Number of Payments - How many payments from lease start date (#12) to lease end date including extensions (#15)?"]
["-  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 (#1g) carefully.",30]
[]
["19.  Was the first lease payment received at the beginning or at the end of the first payment period (a period is the length of time between payments - see #17 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(B117=\"Begin\",1,0)","v":1},{"$f":"IF(C117=\"Begin\",1,0)","v":0},0,{"$f":"IF(E117=\"Begin\",1,0)","v":0},0,0,0,0,0,0,0]
[]
["20.  When is the first payment due (most likely would be within FY2024)?","2023-10-15"]
[]
["VI.  Lease Receivable"]
["Lease payment to be received - PLEASE SEPARATE PAYMENTS TO BE RECEIVED BETWEEN #21, #22 & #23 as much as possible:"]
["21.  Fixed Lease payment - Enter FIRST Fixed Lease Payment Amount here"]
["To calculate the Fixed Lease Payment:\n(a) Enter the amount of the fixed lease portion of the payment.  If the payment includes utilities, taxes, service costs,\n      etc, and it is not reasonable to separate these portions from the total payment, then enter the total lease\n      payment.  If the above listed items are separated in the contract, they should not be included in the lease\n      payment here but detailed below.\n(b) In addition, also include \"Fixed In-Substance Fees\" with the lease payment (see definition below under variable\n      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 as of the start of the lease.",282000]
["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 (#1f) to describe how often payments increase, formulas, appraisal requirements, etc."]
[]
["22.  Variable Usage Fees over and above Fixed In-Substance Fees:"]
["\"Fixed in-Substance Fees\" are the Mimimum Usage Costs of a variable usage fee and should be included in the fixed lease payment portion above.  For Example, if a copier has a $.01/copy variable monthly fee, but minimum is $500, then $500 is the \"Fixed In-Substance Fee\" and should be added to fixed lease portion above, and the variable answer here would be \"$.01/copy over 50,000 copies due to $500 minimum\" ($500 is equal to $.01/copy * 50,000).  Per GASB 87, also include how the fee is determined.\n(Do NOT include Variable Fees on PV Calc tab, but DO include Fixed In-Substance Fees)","% of gross revenues req'd with each payment, minimum included in Fixed Pmt above"]
[]
["23.  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":"282000*0.025","v":7050}]
[]
["b.  Please briefly describe the additional charges.","2.5% occupancy tax"]
[]
["24.  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, is the residual value guarantee payment fixed in substance? (i.e. not contingent on a future event)."]
["For example, a residual value guarantee could have both a minimum amount to be paid upon termination of the lease plus a contingent fee based on the future value of the asset upon termination of the lease.  If the residual value guarantee has no minimum, but is fully contingent on the future value, then it is not fixed in substance and should NOT be included in the Lease Receivable calculation.\nThis is different than on a payable lease where the inclusion in the liability is based on a reasonably certain occurence, not a fixed in substance occurrence.","N/A"]
["If Yes, then please enter the amount of the Fixed in Substance Residual Value Guarantee on the PV Calc Tab."]
[]
["25.  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"]
[]
["b.  If Yes, please describe:","Free Rent Period for pmt due 10/15/2025"]
["Please read instructions on PV Calc tab carefully on how to include lease incentives in the Payment Schedule (#1d & 1e)."]
[]
["26.  Estimated Uncollectible Amounts"]
["Based on current economic factors, lessee's credit rating, historical information from this or other lessees, does the agency feel that there may be uncollectible amounts related to this lease agreement?","Yes - Fill out PV Calc Tab"]
["If Yes, then please enter the Allowance for Uncollectible Leases on the PV Calc Tab."]
[]
["VII.  Interest Rate"]
["27.  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 Implicit Rate info tab for additional information and\n     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 #16 above for ease of reference).",{"$f":"B107","v":180},1,{"$f":"D107","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.",3.3234E-2]
[]
[]
[]
[]
[]
["The interest rates have been updated for"]
["FY2024 & 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"]
[]
["28.  Periodic Interest Rate - based on frequency of payments (will automatically fill)",{"$f":"ROUND(B159/B111,6)","v":1.6617E-2},0,{"$f":"ROUND(D159/D111,6)","v":0},0,0,0,0,0,0,0,0]
[]
["VIII.  Calculate the Lease Liability"]
["29.  Fill out the Lease Payment Schedule on the \"PV Calc\" tab corresponding to the correct Lease #."]
["The Present Value of the Lease Receivable, Amortization Table and Deferred Inflow of Resources 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 (complex)","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":124,"n_cols":12,"rows_kept":124,"cut":false,"hidden":false,"merged":[[0,0,0,1],[2,0,2,1],[4,0,4,1],[5,0,5,1]]}
["GASB 87 Lease Collection Form - General Extra Info - for Leases receiving payments into multiple COAs"]
[]
["If the lease payments received are recorded within more than one Chart of Accounts (COA) string, 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 - When payments are split between multiple COAs, please ONLY provide split information for the Fixed amount to be received.  Please exclude any variable payments or payments for services, maintenance, taxes, etc that are identified separately within the lease contract."]
[]
["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":"ABC 123"},{"$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 received divided by a Percentage (%), or a Specific Dollar ($) Amount (i.e. 50% posted to one COA string, and 50% posted to another; or first $1,000 posted to one COA string and the rest posted to another)?","Percentage (%) Split"]
["Based on your answer, please fill out the proper section below (Percentage Split or Dollar Amount Split)"]
[]
["Percentage (%) Split:"]
["How many different sets of COAs are there?",2]
[]
["1 - Percentage of payment received for First COA",0.7]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["1a - Agency *","ADA"]
["1b - Fund *","AD1234"]
["1c - Revenue Source *",4632]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
[]
["2 - Percentage of payment received for Second COA",0.3]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["2a - Agency *","DCA"]
["2b - Fund *","DC4567"]
["2c - Revenue Source *",4632]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
[]
["3 - Percentage of payment received for Third COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Revenue Source *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
[]
["4 - Percentage of payment received for Fourth COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Revenue Source *"]
["4d - Appropriation"]
["4e - Unit "]
["4f - Task "]
["4g - Sub Task"]
["4h - Program"]
["4i - Phase"]
["4j - Program Period"]
["4k - Dept Object"]
[]
["Specific Dollar ($) Amount Split:"]
["How many different sets of COAs are there?"]
[]
["1 - Amount of payment received for First COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["1a - Agency *"]
["1b - Fund *"]
["1c - Revenue Source *"]
["1d - Appropriation"]
["1e - Unit"]
["1f - Task"]
["1g - Sub Task"]
["1h - Program"]
["1i - Phase"]
["1j - Program Period"]
[]
["2 - Amount of payment received for Second COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["2a - Agency *"]
["2b - Fund *"]
["2c - Revenue Source *"]
["2d - Appropriation"]
["2e - Unit "]
["2f - Task "]
["2g - Sub Task"]
["2h - Program"]
["2i - Phase"]
["2j - Program Period"]
[]
["3 - Amount of payment received for Third COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["3a - Agency *"]
["3b - Fund *"]
["3c - Revenue Source *"]
["3d - Appropriation"]
["3e - Unit "]
["3f - Task "]
["3g - Sub Task"]
["3h - Program"]
["3i - Phase"]
["3j - Program Period"]
[]
["4 - Amount of payment received for Fourth COA"]
["Please provide COA below.  REQUIRED elements are starred (*). Use any other COA indicated below that will help identify the payments received for each lease."]
["4a - Agency *"]
["4b - Fund *"]
["4c - Revenue Source *"]
["4d - Appropriation"]
["4e - Unit "]
["4f - Task "]
["4g - Sub Task"]
["4h - Program"]
["4i - Phase"]
["4j - Program Period"]
["4k - Dept Object"]

{"sheet":"Implicit Rate info","index":2,"n_rows":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":"ABC 123"},{"$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 V, #19 of Data Input tab)",{"$f":"'Data Input'!B117","v":"Begin"},{"$f":"'Data Input'!C117","v":0},{"$f":"'Data Input'!D117","v":0},{"$f":"'Data Input'!E117","v":0},{"$f":"'Data Input'!F117","v":0},{"$f":"'Data Input'!G117","v":0},{"$f":"'Data Input'!H117","v":0},{"$f":"'Data Input'!I117","v":0},{"$f":"'Data Input'!J117","v":0},{"$f":"'Data Input'!K117","v":0},{"$f":"'Data Input'!L117","v":0}]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!B114","v":30},{"$f":"'Data Input'!C114","v":0},{"$f":"'Data Input'!D114","v":0},{"$f":"'Data Input'!E114","v":0},{"$f":"'Data Input'!F114","v":0},{"$f":"'Data Input'!G114","v":0},{"$f":"'Data Input'!H114","v":0},{"$f":"'Data Input'!I114","v":0},{"$f":"'Data Input'!J114","v":0},{"$f":"'Data Input'!K114","v":0},{"$f":"'Data Input'!L114","v":0}]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!B110","v":"Semi-annually"},{"$f":"'Data Input'!C110","v":0},{"$f":"'Data Input'!D110","v":0},{"$f":"'Data Input'!E110","v":0},{"$f":"'Data Input'!F110","v":0},{"$f":"'Data Input'!G110","v":0},{"$f":"'Data Input'!H110","v":0},{"$f":"'Data Input'!I110","v":0},{"$f":"'Data Input'!J110","v":0},{"$f":"'Data Input'!K110","v":0},{"$f":"'Data Input'!L110","v":0}]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!B120","v":"2023-10-15"},{"$f":"'Data Input'!C120","v":"00:00:00"},{"$f":"'Data Input'!D120","v":"00:00:00"},{"$f":"'Data Input'!E120","v":"00:00:00"},{"$f":"'Data Input'!F120","v":"00:00:00"},{"$f":"'Data Input'!G120","v":"00:00:00"},{"$f":"'Data Input'!H120","v":"00:00:00"},{"$f":"'Data Input'!I120","v":"00:00:00"},{"$f":"'Data Input'!J120","v":"00:00:00"},{"$f":"'Data Input'!K120","v":"00:00:00"},{"$f":"'Data Input'!L120","v":"00:00:00"}]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!B96","v":"2023-10-01"},{"$f":"'Data Input'!C96","v":"00:00:00"},{"$f":"'Data Input'!D96","v":"00:00:00"},{"$f":"'Data Input'!E96","v":"00:00:00"},{"$f":"'Data Input'!F96","v":"00:00:00"},{"$f":"'Data Input'!G96","v":"00:00:00"},{"$f":"'Data Input'!H96","v":"00:00:00"},{"$f":"'Data Input'!I96","v":"00:00:00"},{"$f":"'Data Input'!J96","v":"00:00:00"},{"$f":"'Data Input'!K96","v":"00:00:00"},{"$f":"'Data Input'!L96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!B105","v":"2038-09-30"},{"$f":"'Data Input'!C105","v":"1900-01-30"},{"$f":"'Data Input'!D105","v":"1900-01-30"},{"$f":"'Data Input'!E105","v":"1900-01-30"},{"$f":"'Data Input'!F105","v":"1900-01-30"},{"$f":"'Data Input'!G105","v":"1900-01-30"},{"$f":"'Data Input'!H105","v":"1900-01-30"},{"$f":"'Data Input'!I105","v":"1900-01-30"},{"$f":"'Data Input'!J105","v":"1900-01-30"},{"$f":"'Data Input'!K105","v":"1900-01-30"},{"$f":"'Data Input'!L105","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 VII, #27) 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":139,"n_cols":16,"rows_kept":139,"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":"ABC 123"},null,"Present Value Table",null,null,null,null,"Amortization Table"]
["Periodic Interest Rate (Section VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!B171","v":1.6617E-2},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 V, #19)",{"$f":"'Data Input'!B117","v":"Begin"},"Important:",null,1,282000,{"$f":"F3 / (((1+$B$2)) ^ (E3-1))","v":282000},null,null,1,{"$f":"B6","v":"2023-10-15"},{"$f":"B25","v":6757100.0807918618},282000,{"$f":"M3-O3","v":282000},0,{"$f":"L3-N3","v":6475100.0807918618}]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!B114","v":30},"Do not insert/delete/move",null,2,282000,277390.60039326508,null,null,2,{"$f":"EDATE($B$6,6)","v":"2024-04-15"},{"$f":"P3","v":6475100.0807918618},282000,174403.26,{"$f":"ROUND(L4*$B$2,2)","v":107596.74},6300696.820791862]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!B110","v":"Semi-annually"},"cells B2:B8",null,3,282000,272856.54321466695,null,null,3,{"$f":"EDATE($B$6,12)","v":"2024-10-15"},6300696.820791862,282000,177301.32,104698.68,6123395.5007918617]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!B120","v":"2023-10-15"},"(macros depend on them)",null,4,282000,268396.59696293384,null,null,4,{"$f":"EDATE($B$6,18)","v":"2025-04-15"},6123395.5007918617,282000,180247.53999999998,101752.46,5943147.9607918616]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!B96","v":"2023-10-01"},null,null,5,0,0,null,null,5,{"$f":"EDATE($B$6,24)","v":"2025-10-15"},5943147.9607918616,0,-98757.29,98757.29,6041905.2507918617]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!B105","v":"2038-09-30"},null,null,6,282000,259694.21155278833,null,null,6,{"$f":"EDATE($B$6,30)","v":"2026-04-15"},6041905.2507918617,282000,181601.66,100398.34,5860303.5907918615]
[null,null,null,null,7,282000,255449.40872795586,null,null,7,{"$f":"EDATE($B$6,36)","v":"2026-10-15"},5860303.5907918615,282000,184619.34,97380.66,5675684.2507918617]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & see instructions below):","Amount","# of Payments",null,8,282000,251273.9888551498,null,null,8,{"$f":"EDATE($B$6,42)","v":"2027-04-15"},5675684.2507918617,282000,187687.15,94312.85,5487997.1007918613]
["Payment Amount #1 & # of Payments at this amount",282000,4,null,9,282000,247166.81784305186,null,null,9,{"$f":"EDATE($B$6,48)","v":"2027-10-15"},5487997.1007918613,282000,190805.95,91194.05,5297191.1507918611]
["Payment Amount #2 & # of Payments at this amount",0,1,null,10,282000,243126.7801375069,null,null,10,{"$f":"EDATE($B$6,54)","v":"2028-04-15"},5297191.1507918611,282000,193976.57,88023.43,5103214.5807918608]
["Payment Amount #3 & # of Payments at this amount",282000,5,null,11,302000,256113.96837728692,null,null,11,{"$f":"EDATE($B$6,60)","v":"2028-10-15"},5103214.5807918608,302000,217199.88,84800.12,4886014.7007918609]
["Payment Amount #4 & # of Payments at this amount",302000,20,null,12,302000,251927.68601871392,null,null,12,{"$f":"EDATE($B$6,66)","v":"2029-04-15"},4886014.7007918609,302000,220809.09,81190.91,4665205.6107918611]
["Totals from add'l table below if needed)",{"$f":"B88","v":0},{"$f":"C88","v":0},null,13,302000,247809.83007240086,null,null,13,{"$f":"EDATE($B$6,72)","v":"2029-10-15"},4665205.6107918611,302000,224478.28,77521.72,4440727.3307918608]
["Total Lease Payments (sum of all payment amounts * # of payments)",{"$f":"(B11*C11)+(B12*C12)+(B13*C13)+(B14*C14)+(B15)","v":8578000},{"$f":"SUM(C11:C15)","v":30},null,14,302000,243759.2820820435,null,null,14,{"$f":"EDATE($B$6,78)","v":"2030-04-15"},4440727.3307918608,302000,228208.43,73791.570000000007,4212518.9007918611]
[null,null,null,"Response per",15,302000,239774.94187294081,null,null,15,{"$f":"EDATE($B$6,84)","v":"2030-10-15"},4212518.9007918611,302000,232000.57,69999.429999999993,3980518.3307918613]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab",16,302000,235855.72725317479,null,null,16,{"$f":"EDATE($B$6,90)","v":"2031-04-15"},3980518.3307918613,302000,235855.72999999998,66144.27,3744662.6007918613]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24)",0,null,{"$f":"'Data Input'!B141","v":"N/A"},17,302000,232000.57371967498,null,null,17,{"$f":"EDATE($B$6,96)","v":"2031-10-15"},3744662.6007918613,302000,239774.94,62225.06,3504887.6607918614]
["Allowance for Uncollectible Leases (Section VI, #26)",null,null,null,18,302000,228208.43416908724,null,null,18,{"$f":"EDATE($B$6,102)","v":"2032-04-15"},3504887.6607918614,302000,243759.28,58240.72,3261128.3807918616]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,{"$f":"B16*0.002","v":17156},{"$f":"'Data Input'!B151","v":"Yes - Fill out PV Calc Tab"},19,302000,224478.27861336889,null,null,19,{"$f":"EDATE($B$6,108)","v":"2032-10-15"},3261128.3807918616,302000,247809.83000000002,54190.17,3013318.5507918615]
["Total Other Payment Considerations",{"$f":"SUM(B19:B21)","v":0},null,null,20,302000,220809.09390003202,null,null,20,{"$f":"EDATE($B$6,114)","v":"2033-04-15"},3013318.5507918615,302000,251927.69,50072.31,2761390.8607918615]
["Total Undiscounted Cash Flows (Total Payments + Other Considerations)",{"$f":"B16+B22","v":8578000},null,null,21,302000,217199.88343696005,null,null,21,{"$f":"EDATE($B$6,120)","v":"2033-10-15"},2761390.8607918615,302000,256113.97,45886.03,2505276.8907918613]
[null,null,"Important:",null,22,302000,213649.66692172177,null,null,22,{"$f":"EDATE($B$6,126)","v":"2034-04-15"},2505276.8907918613,302000,260369.81,41630.19,2244907.0807918613]
["Net Present Value (calculated after Macros below run)",{"$f":"SUM(G:G)","v":6757100.0807918618},"Do not insert/delete/move",null,23,302000,210157.48007531033,null,null,23,{"$f":"EDATE($B$6,132)","v":"2034-10-15"},2244907.0807918613,302000,264696.38,37303.620000000003,1980210.7007918614]
[null,null,"NPV must be cell B25",null,24,302000,206722.37438023396,null,null,24,{"$f":"EDATE($B$6,138)","v":"2035-04-15"},1980210.7007918614,302000,269094.83999999997,32905.160000000003,1711115.8607918615]
["Additions/Subtractions for Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!B75-'Data Input'!B76","v":287000},"(macros depend on it)",null,25,302000,203343.41682288807,null,null,25,{"$f":"EDATE($B$6,144)","v":"2035-10-15"},1711115.8607918615,302000,273566.39,28433.61,1437549.4707918614]
["Deferred Inflow of Resources (calculated after Macros below run)",{"$f":"B25+B27","v":7044100.0807918618},null,null,26,302000,200019.68964013786,null,null,26,{"$f":"EDATE($B$6,150)","v":"2036-04-15"},1437549.4707918614,302000,278112.24,23887.759999999998,1159437.2307918614]
[null,null,null,null,27,302000,196750.29007004393,null,null,27,{"$f":"EDATE($B$6,156)","v":"2036-10-15"},1159437.2307918614,302000,282733.63,19266.37,876703.60079186142]
["The Agency is not required to calculate the Present Value and/or enable Macros,",null,null,null,28,302000,193534.33010666151,null,null,28,{"$f":"EDATE($B$6,162)","v":"2037-04-15"},876703.60079186142,302000,287431.82,14568.18,589271.78079186147]
["but Steps 1 & 2 of the instructions below are REQUIRED to be completed.",null,null,null,29,302000,190370.93625884826,null,null,29,{"$f":"EDATE($B$6,168)","v":"2037-10-15"},589271.78079186147,302000,292208.07,9791.93,297063.71079186146]
["After completing Steps 1 & 2, if desired to calculate the Present Value, complete Steps 3 - 8:",null,null,null,30,302000,187259.24931301386,null,null,30,{"$f":"EDATE($B$6,174)","v":"2038-04-15"},297063.71079186146,302000,297063.71000000002,{"$f":"ROUND(L32*$B$2,2)-0.02","v":4936.29},7.9186144284904003E-4]
[]
["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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!B147","v":"Free Rent Period for pmt due 10/15/2025"}]
["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 5 yrs = $282,000; Payment for yrs 6-10 = $302,000; extension period amount to be negotiated - therefore, using most recent payment amount before extension period of $302,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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!C171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!C117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!C114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!C110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!C120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!C96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!C105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!C141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!C151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!C75-'Data Input'!C76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!C147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!D171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!D117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!D114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!D110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!D120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!D96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!D105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!D141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!D151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!D75-'Data Input'!D76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!D147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!E171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!E117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!E114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!E110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!E120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!E96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!E105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!E141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!E151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!E75-'Data Input'!E76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!E147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!F171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!F117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!F114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!F110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!F120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!F96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!F105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!F141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!F151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!F75-'Data Input'!F76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!F147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!G171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!G117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!G114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!G110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!G120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!G96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!G105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!G141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!G151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!G75-'Data Input'!G76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!G147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!H171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!H117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!H114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!H110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!H120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!H96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!H105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!H141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!H151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!H75-'Data Input'!H76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!H147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!I171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!I117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!I114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!I110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!I120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!I96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!I105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!I141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!I151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!I75-'Data Input'!I76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!I147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!J171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!J117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!J114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!J110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!J120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!J96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!J105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!J141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!J151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!J75-'Data Input'!J76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!J147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!K171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!K117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!K114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!K110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!K120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!K96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!K105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!K141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!K151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!K75-'Data Input'!K76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!K147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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":139,"n_cols":4,"rows_kept":139,"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 VII, #27-28 from Data Input tab)",{"$f":"'Data Input'!L171","v":0}]
["Cash Flows at Beginning or End (Section V, #19)",{"$f":"'Data Input'!L117","v":0},"Important:"]
["Number of payments (Section V, #18)",{"$f":"'Data Input'!L114","v":0},"Do not insert/delete/move"]
["Frequency of payments (Section V, #17)",{"$f":"'Data Input'!L110","v":0},"cells B2:B8"]
["Date of first payment (Section V, #20)",{"$f":"'Data Input'!L120","v":"00:00:00"},"(macros depend on them)"]
["Lease Start Date (Section V, #12)",{"$f":"'Data Input'!L96","v":"00:00:00"}]
["Lease End Date including Extensions (Section V, #15)",{"$f":"'Data Input'!L105","v":"1900-01-30"}]
[]
["Fixed Lease Payment Receivable Schedule (Section VI, #21 & 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}]
[null,null,null,"Response per"]
["Other Payment Considerations:","Residual Value Guar","Allow for Uncollectible Amt","Data Input tab"]
["Residual Value Guarantee if Fixed-in-Substance (Section VI, #24a & b)",0,null,{"$f":"'Data Input'!L141","v":0}]
["Allowance for Uncollectible Leases (Section VI, #26)"]
["For example, could be calculated as a percentage of the total lease pmt rcvbl above",null,0,{"$f":"'Data Input'!L151","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 Deferred Inflow of Resources (Section IV, #9-11)",{"$f":"'Data Input'!L75-'Data Input'!L76","v":0},"(macros depend on it)"]
["Deferred Inflow of Resources (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 Receivable Schedule\" section above (starting on row 10)."]
["a - If payments escalate over time, list beginning payment amount & # of payments to be received at that amount, then move to line #2"]
["and list the next payment amount & # of payments at that amount, etc."]
["b - If an extension is deemed Reasonably Certain to be exercised, but the amount is listed as \"negotiable\", then use the lease"]
["payment amount just prior to the date of extension for those unknown amounts."]
["c - If a future payment is variable based on an index or interest rate, then calculate those future payments using the current 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'!L147","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 Receivable Lines (if needed)","Amount","# of Payments"]
["Payment Amount #5 & # of Payments at this amount",0,0]
["Payment Amount #6 & # of Payments at this amount",0,0]
["Payment Amount #7 & # of Payments at this amount",0,0]
["Payment Amount #8 & # of Payments at this amount",0,0]
["Payment Amount #9 & # of Payments at this amount",0,0]
["Payment Amount #10 & # of Payments at this amount",0,0]
["Payment Amount #11 & # of Payments at this amount",0,0]
["Payment Amount #12 & # of Payments at this amount",0,0]
["Payment Amount #13 & # of Payments at this amount",0,0]
["Payment Amount #14 & # of Payments at this amount",0,0]
["Payment Amount #15 & # of Payments at this amount",0,0]
["Payment Amount #16 & # of Payments at this amount",0,0]
["Payment Amount #17 & # of Payments at this amount",0,0]
["Payment Amount #18 & # of Payments at this amount",0,0]
["Payment Amount #19 & # of Payments at this amount",0,0]
["Payment Amount #20 & # of Payments at this amount",0,0]
["Payment Amount #21 & # of Payments at this amount",0,0]
["Payment Amount #22 & # of Payments at this amount",0,0]
["Payment Amount #23 & # of Payments at this amount",0,0]
["Payment Amount #24 & # of Payments at this amount",0,0]
["Payment Amount #25 & # of Payments at this amount",0,0]
["Total Add'l Lease Payment Amts (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"]
["(responses from Data Input tab are shown in col D)."]
["a - The Residual Value Guarantee line should only have an amount listed in Col B if it is Fixed In-Substance and not reliant on a future"]
["consideration (i.e. FMV of leased asset)."]
["b - The Allowance for Uncollectible Leases line should only have an amount listed in col C if economic circumstances, lessee"]
["creditworthiness, and historic information indicate a need for an allowance to be calculated.  This amount is listed in col C because"]
["it will not be calculated as part of the Present Value of the Lease Receivable, but will instead reduce the Present Value after it is"]
["calculated as a contra-receivable."]
[]
["Steps 3 - 8 are OPTIONAL (calculation of Present Value and Amortization Tables)"]
["Step 3 - Click the Macro button below to Generate the first 2 columns of the Present Value (PV) Table."]
[]
[]
["Step 4 - Using the Payment Receivable Schedule filled out in Steps 1 & 2, fill in the Payment Amounts into 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 Residual Value Guarantee listed for $5,000, then the final payment in the Table should be $25,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 (columns K & M) to make sure the payments change on the correct dates as per the Lease Contract."]
["c - If 6a and 6b are BOTH correct, skip to Step 8 (Do NOT click the Start Over Macro Button)."]
["d - If 6a or 6b does not appear to be correct:"]
["i - Double-check the Instructions above (Steps 1 - 6), including the check for # of payments in 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 Receivable and the Deferred Inflow of Resources 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","Manufactured Building (non-permanent) - Office Space",300]
["Vehicles","Aircraft","Vehicles - Aircraft",180]
["Vehicles","Heavy Duty Truck","Vehicles - Heavy Duty Truck",84]
["Vehicles","Regular truck/car","Vehicles - Regular truck/car",60]
["Equipment","Furniture","Equipment - Furniture",60]
["Equipment","IT Equipment","Equipment - IT Equipment",60]
["Equipment","Telecommunications","Equipment - Telecommunications",60]
["Equipment","Other Office Equipment","Equipment - Other Office Equipment",60]
["Land",null,"Land","N/A"]
[null,null,"Right-of-way","N/A"]
[]
["Fixed Asset Catalog Description",null,"Useful Life in Months"]
["Leased Constructed Buildings (Office Space) ",null,480]
["Leased Buildings: Prefabricated (Office Space)",null,300]
["Leased Mainframe Computer",null,60]
["Leased Servers",null,60]
["Leased Desktop Computer",null,60]
["Leased Multifunction (print/scan/fax/copy) workgroup printer",null,60]
["Leased Airplane",null,180]
["Leased Automobiles & Station Wagons",null,60]
["Leased Trucks (Heavy)",null,84]
["Leased Mass Transportation: Transit Bus",null,84]
["Leased Radio, Sound and Telecomm. Equipment",null,60]
["Leased Land Improvements",null,480]
["Leased Improvements Other than Land & Building Improvements",null,180]
["Leased Tangible Rights of Way ",null,"Use contract term"]
["Leased Land ",null,"Use contract term"]

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