| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | AE | AF | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Please note: this is the formula that applies to federal aid for school year 2023-24. For the 2024-25 FAFSA, which will open in December 2023, the new formula will be in effect. As of September 2023, I have not yet completed a spreadsheet for the new FAFSA. | |||||||||||||||||||||||||||||||
2 | To use this spreadsheet, you must first make your own copy; to do so, you need to be logged into a Google Docs account. With the Google Sheets "File" menu, choose File → Make a Copy. Do this on a computer, not a phone. If the Google Sheets "File" menu is not visible, click the "Show the Menus" button (⌄) at the top-right of the window, or try Control-Shift-F. It is also possible to download it in Excel format, but some things export poorly and I recommend using Google Sheets if possible. See https://bit.ly/UsingTheSpreadsheet for additional advice. | |||||||||||||||||||||||||||||||
3 | For a prose explanation of how the EFC formula and this spreadsheet work, go to: | http://bit.ly/EFC-explanation | For a slide deck explaining EFC, go to: | https://bit.ly/EFC-slides | ||||||||||||||||||||||||||||
4 | For detailed directions on what to include in each line, see the FAFSA PDF: | https://studentaid.gov/sites/default/files/2023-24-fafsa.pdf | https://bit.ly/UsingTheSpreadsheet | |||||||||||||||||||||||||||||
5 | PARENT INCOME CALCULATIONS | PARENT ASSET CALCULATIONS | STUDENT INCOME AND ASSET CALCULATIONS | Child 1 EFC | $12,460 | |||||||||||||||||||||||||||
6 | Line #s | Line #s | Only enter data in yellow and turquoise cells! Do NOT override fomulas by accident! | Line #s | Child 1 | Child 2 | Child 3 | Child 4 | Portion of EFC attributable to PARENT … | |||||||||||||||||||||||
7 | E: | F: | E: EFC Formula Line numbers from https://ifap.ed.gov/efcformulaguide/attachments/2021EFCFormulaGuide.pdf | F: | E: | F: | Income | $12,390 | 99% | 13.5% | ||||||||||||||||||||||
8 | 1 | 81 | Parents' Adjusted Gross Income (1040:11) | $90,000 | 16 | 85 | Cash, Checking, Savings | $2,000 | 29 | 33 | Student Adjusted Gross Income | $7,400 | Assets | $70 | 1% | 3.5% | ||||||||||||||||
9 | 2a | 83 | Parent 1 earned income (see https://bit.ly/FAFSA-earned) | $45,000 | 17 | 86 | Investments (include 529s but not IRAs/401(k)s) | 30 | 35 | Student Earned Income | $7,400 | Total | $12,460 | 100% | ||||||||||||||||||
10 | 2b | 84 | Parent 2 earned income (see https://bit.ly/FAFSA-earned) | $45,000 | 18 | 87 | Business / Farm | $0 | 31 | Student "Taxable Income" (really Student AGI) | $7,400 | Portion of EFC attributable to STUDENT … | ||||||||||||||||||||
11 | Total parent earned income | $90,000 | 19 | Adjusted Business/Farm (Table A4) | $0 | 32 | Student Untaxed Income | $0 | $0 | $0 | $0 | Income | $0 | N/A | 0.0% | |||||||||||||||||
12 | 3 | "Taxable Income" (really AGI) | $90,000 | 20 | Net Worth | $2,000 | 41a | 401(k) and similar contributions | $0 | Assets | $0 | N/A | N/A | |||||||||||||||||||
13 | 4 | Untaxed income and benefits | $2,000 | 21 | Asset protection allowance (Table A5) | $0 | 41b | Tax-deductible IRA and similar contributions | $0 | Total | $0 | 0% | ||||||||||||||||||||
14 | 89a | Traditional 401(k) and similar contributions (W-2: 12 D/E/F/G/H/S) | $2,000 | Age of older parent on 12/31/2023 | 50 | 41c | Child support receieved | $0 | ||||||||||||||||||||||||
15 | 89b | Tax-deductible IRA and similar contributions (1040 Sch1: 16 +20) | $0 | Number of parents on FAFSA | 2 | 41d | Tax-exempt interest | $0 | Child 2 EFC | N/A | ||||||||||||||||||||||
16 | 89c | Child support receieved | $0 | 22 | PARENT ASSESSABLE ASSETS | $2,000 | 41e | Untaxed IRA and pension distributions | $0 | Portion of EFC attributable to PARENT … | ||||||||||||||||||||||
17 | 89d | Tax-exempt interest (1040: 2a) | $0 | 23 | Asset conversion rate | 0.12 | 41f | Housing and Food allowances | $0 | Income | $0 | 99% | 0.0% | |||||||||||||||||||
18 | 89e | Untaxed IRA and pension distributions (1040: 4a+5a - (4b+5b)) | $0 | 24 | "Contribution" [to AAI] from Assets | $240 | 41g | Veterans noneducation benefits | $0 | Assets | $0 | 1% | 0.0% | |||||||||||||||||||
19 | 89f | Housing and food allowances | $0 | 41h | Other untaxed income | $0 | Total | $0 | N/A | |||||||||||||||||||||||
20 | 89g | Veterans noneducation benefits | $0 | PARENT AAI (INCOME AND ASSETS) CALCULATIONS | 41i | Money not reported elsewhere (includes third-party 529 disbursements from 2021) | $0 | Portion of EFC attributable to STUDENT … | ||||||||||||||||||||||||
21 | 89h | Other untaxed income | $0 | Income | $0 | N/A | N/A | |||||||||||||||||||||||||
22 | 5 | Taxable and untaxed income | $92,000 | 25 | AAI ("Adjusted available income") | $42,765 | 33 | Taxable and untaxed income | $7,400 | $0 | $0 | $0 | Assets | $0 | N/A | N/A | ||||||||||||||||
23 | 6 | Additional financial information | $0 | 26 | Parent Component of Total EFC (aka Total contribution from AAI, Table A6) | $12,460 | 29% | 34 | Additional financial information | $0 | $0 | $0 | $0 | Total | $0 | N/A | ||||||||||||||||
24 | 88a | Education credits (1040 Schedule 3:3) | $0 | 40a | Child's Education credits | $0 | ||||||||||||||||||||||||||
25 | 88b | Child support paid | $0 | Portion attributable to Income | $12,390 | 99% | 13.5% | 40b | Child support paid by child | $0 | Child 3 EFC | N/A | ||||||||||||||||||||
26 | 88c | Parents' Work Study Income | $0 | Portion attributable to Assets | $70 | 1% | 3.5% | 40c | Child's Work Study Income | $0 | Portion of EFC attributable to PARENT … | |||||||||||||||||||||
27 | 88d | Parent taxable college grant aid | $0 | 27 | Number in college | 1 | 40d | Child taxable college grant aid | $0 | Income | $0 | 99% | 0.0% | |||||||||||||||||||
28 | 88e | Combat pay (taxable only) | $0 | 28 | Parent Component of Each Student's EFC | $12,460 | 40e | Child Combat pay | $0 | Assets | $0 | 1% | 0.0% | |||||||||||||||||||
29 | 88f | Parent earnings from coop work | $0 | Portion attributable to Income | $12,390 | 40f | Child earnings from coop work | $0 | Total | $0 | N/A | |||||||||||||||||||||
30 | 7 | TOTAL PARENT INCOME | $92,000 | Portion attributable to Assets | $70 | 35 | TOTAL STUDENT INCOME | $7,400 | $0 | $0 | $0 | Portion of EFC attributable to STUDENT … | ||||||||||||||||||||
31 | Estimate income tax based on income? | 36 | 34 | US Income Tax paid | $0 | Income | $0 | N/A | N/A | |||||||||||||||||||||||
32 | Filing Status | MFJ | Child 1 EFC | $12,460 | 37 | State and other tax allowance | $222.00 | $0.00 | $0.00 | $0.00 | Assets | $0 | N/A | N/A | ||||||||||||||||||
33 | Standard Deduction | $25,100 | Portion of EFC attributable to PARENT … | 18 | Student state of legal residence | OH | OH | OH | OH | Total | $0 | N/A | ||||||||||||||||||||
34 | AGI - Standard Deduction (taxable income) | $64,900 | Income | $12,390 | 99% | Student state tax allowance rate (Table A7) | 3% | 3% | 3% | 3% | ||||||||||||||||||||||
35 | Typical federal income tax on income entered | $7,390 | 11.4% | Assets | $70 | 1% | 38 | Child Social Security Allowance | $566 | $0 | $0 | $0 | Child 4 EFC | N/A | ||||||||||||||||||
36 | # of children for 2021 child tax credit | 1 | Total | $12,460 | 100% | 39 | Income protection allowance | $7,600 | $7,600 | $7,600 | $7,600 | Portion of EFC attributable to PARENT … | ||||||||||||||||||||
37 | Child tax credit | $2,000 | Portion of EFC attributable to STUDENT … | 40 | Allowance for parents' negative adjusted available income | $0 | $0 | $0 | $0 | Income | $0 | 99% | 0.0% | |||||||||||||||||||
38 | Estimated FIT after child tax credit | $5,390 | 8.3% | Income | $0 | N/A | 0.0% | 41 | TOTAL STUDENT ALLOWANCE | $8,388 | $7,600 | $7,600 | $7,600 | Assets | $0 | 1% | 0.0% | |||||||||||||||
39 | 8 | 82 | US Income Tax paid (1040:22-Sch2:2) (ACTUAL) | $1,600 | Assets | $0 | N/A | N/A | 42 | Student Available Income (AI) | -$988 | -$7,600 | -$7,600 | -$7,600 | Total | $0 | N/A | |||||||||||||||
40 | 8 | 82 | US Income Tax paid (actual OR estimated) | $1,600 | Total | $0 | 0% | 43 | Assessment of Available Income | 0.50 | 0.50 | 0.50 | 0.50 | Portion of EFC attributable to STUDENT … | ||||||||||||||||||
41 | 9 | State and other tax allowance | $3,680 | 44 | Student component from available Income | $0 | $0 | $0 | $0 | Income | $0 | N/A | N/A | |||||||||||||||||||
42 | 66 | Parent state of legal residence | OH | Color and Border Coding | Assets | $0 | N/A | N/A | ||||||||||||||||||||||||
43 | 0-$14,999 Rate from Table A1 | 5% | More commonly needed inputs | Only edit values in the cells that are formatted like those to the immediate left. | 45 | 37 | Student cash, savings, and checking | $0 | Total | $0 | N/A | |||||||||||||||||||||
44 | $15,000+ Rate from Table A1 | 4% | Less commonly needed inputs (often $0) | 46 | 38 | Student investments | $0 | |||||||||||||||||||||||||
45 | 10 | Parent 1 Social Security Allowance | $3,443 | Calculated Fields (don't edit these) | 47 | 39 | Student business / farm | $0 | ||||||||||||||||||||||||
46 | 11 | Parent 2 Social Security Allowance | $3,443 | Inputs that increase EFC | 48 | Student Net Worth | $0 | $0 | $0 | $0 | ||||||||||||||||||||||
47 | Income Protection Allowance Table A3 | $32,610 | Inputs that decrease EFC | 49 | Assessment Rate | 0.20 | 0.20 | 0.20 | 0.20 | |||||||||||||||||||||||
48 | 72 | Number in household | 4 | Outputs (EFC results and analysis) | 50 | Student component from assets | $0 | $0 | $0 | $0 | ||||||||||||||||||||||
49 | 27 | 73 | Number of college students in household | 1 | Use the links below for specific directions about the values to enter in each box: | |||||||||||||||||||||||||||
50 | Employment expense allowance | $4,700 | EFC Formula (E: line numbers) | https://fsapartners.ed.gov/sites/default/files/2022-08/2324EFCFormulaGuide.pdf | ||||||||||||||||||||||||||||
51 | Pick the situation that applies (A, B, or C) | B | FAFSA form and directions (F: line numbers) | https://studentaid.gov/sites/default/files/2023-24-fafsa.pdf | ||||||||||||||||||||||||||||
52 | A: Two employed parents | $4,700 | This spreadsheet uses the 2023-24 EFC formula. For the 2022-23 formula, use: | https://bit.ly/FAFSA-spreadsheet-2021 | ||||||||||||||||||||||||||||
53 | B: One employed parent | $4,700 | Designed and implemented by George Schneiderman, based on the published EFC Formula. If you find any errors, please report them in a comment. © 2020-22. | |||||||||||||||||||||||||||||
54 | C: Two parents / One employed parent | $0 | For the most current version of this spreadsheet, go to: | https://bit.ly/FAFSA-spreadsheet-2022 | ||||||||||||||||||||||||||||
55 | 14 | TOTAL PARENT ALLOWANCES | $49,475 | For a prose guide to the spreadsheet, go to: | http://bit.ly/EFC-explanation | |||||||||||||||||||||||||||
56 | 15 | PARENT "AVAILABLE INCOME" | $42,525 | For a slide deck and video explaining EFC, go to: | https://bit.ly/EFC-slides | |||||||||||||||||||||||||||
57 | ||||||||||||||||||||||||||||||||
58 | Use the simplifed formula (for those who qualify) | |||||||||||||||||||||||||||||||
59 | The simplified formula considers only income, not assets, but is otherwise the same as the regular formula. It can be used if parent income is under $50,000 and certain other requirements are met. | |||||||||||||||||||||||||||||||
60 | For details, see pages 4-5 of the EFC Formula, or look at the "Simplified Formula" worksheet in this spreadsheet. | |||||||||||||||||||||||||||||||
61 | Checking the box will set all asset totals to zero and black them out. Alternatively, just leave asets blanks. | |||||||||||||||||||||||||||||||
62 | If AGI is under $29,000 and the student qualifies to use the simplified formula, then EFC will automatically be zero. | |||||||||||||||||||||||||||||||
63 | ||||||||||||||||||||||||||||||||
64 | How close did this spreadsheet come to the result you got from FAFSA? Please help me validate it by reporting your results back to me: | https://bit.ly/EFC-survey | ||||||||||||||||||||||||||||||
65 | ||||||||||||||||||||||||||||||||
66 | Would you like to join a group to discuss FAFSA and financial aid generally? | |||||||||||||||||||||||||||||||
67 | Try | the Facebook group Understanding College Aid | ||||||||||||||||||||||||||||||
68 | ||||||||||||||||||||||||||||||||
69 | ||||||||||||||||||||||||||||||||
70 | ||||||||||||||||||||||||||||||||
71 | ||||||||||||||||||||||||||||||||
72 | ||||||||||||||||||||||||||||||||
73 | ||||||||||||||||||||||||||||||||
74 | ||||||||||||||||||||||||||||||||
75 | ||||||||||||||||||||||||||||||||
76 | ||||||||||||||||||||||||||||||||
77 | ||||||||||||||||||||||||||||||||
78 | ||||||||||||||||||||||||||||||||
79 | ||||||||||||||||||||||||||||||||
80 | ||||||||||||||||||||||||||||||||
81 | ||||||||||||||||||||||||||||||||
82 | ||||||||||||||||||||||||||||||||
83 | ||||||||||||||||||||||||||||||||
84 | ||||||||||||||||||||||||||||||||
85 | ||||||||||||||||||||||||||||||||
86 | ||||||||||||||||||||||||||||||||
87 | ||||||||||||||||||||||||||||||||
88 | ||||||||||||||||||||||||||||||||
89 | ||||||||||||||||||||||||||||||||
90 | ||||||||||||||||||||||||||||||||
91 | ||||||||||||||||||||||||||||||||
92 | ||||||||||||||||||||||||||||||||
93 | ||||||||||||||||||||||||||||||||
94 | ||||||||||||||||||||||||||||||||
95 | ||||||||||||||||||||||||||||||||
96 | ||||||||||||||||||||||||||||||||
97 | ||||||||||||||||||||||||||||||||
98 | ||||||||||||||||||||||||||||||||
99 | ||||||||||||||||||||||||||||||||
100 | ||||||||||||||||||||||||||||||||