Personal Finance Project

Personal Finance Project

You will develop an individual ‘life’ financial plan using the excel spreadsheet template provided in the Project D2L folder. You are to fill in (and add rows and columns as needed) the information for both your personal income statement and your personal balance sheet. You will need to add the respective formulas in the spreadsheet, so that the income statement and balance sheet are accurate and “tie” together. For example, your school loans (if you have them or expect them) would go into your liabilities and then you’d have lines in recurring expenses on your income statement as you pay off the loan. Input your expected salaries, costs, deductions, investments, etc. and summarize your assumptions in a clear manner so the logic can be easily understood. You will decide what your expected salary increases will be, when you will buy a house, what the house will cost, how much money you will plan to save in your retirement account vs. your regular savings account and when you will make major purchases. Research median salary data for your expected career and median home prices and/or rent prices in the area you plan to live. You will need to provide an amortization table for any loans that you incur (such as home mortgage and or car payments. You could do this in a separate worksheet in your spreadsheet).

You will also need to determine your income tax rate for each year. This can be found using the current US federal income tax brackets. You will need to research how these work and apply the correct formula in your spreadsheet. For simplicity, state income tax can be ignored.

You are expected to enter the appropriate formulas into the spreadsheet (linking your income statement and balance sheet appropriately) so that you will have a picture of your budget, your growing net worth, and your retirement portfolio. Also, some line items are missing on the spreadsheet and you will need to add them to make your spreadsheet complete and accurate.

Develop a plan that will generate an adequate amount of money to retire at age 55 (if you are currently in your early twenties. If you are older, then you may provide an appropriate retirement age). Complete the analysis out to age 95 to ensure that you will have adequate resources to provide for your entire life. Research what average rate of return can reasonably be expected in your 401k and other investments. Assume that you receive NO social security benefits in later years (therefore, your investments have to provide all your required income in your retirement years). Again…. clearly show your assumptions and explain your logic in the spreadsheet.

For salary increases, you can grow them at some rate higher than inflation, but you may want to limit the salary increases to matching inflation after 45 or 50 years old to be conservative. You need to grow your expenses over time with both inflation and life style. For example, your travel budget should dramatically increase as you get out of school and begin your career/life. Health care should probably grow at 2X inflation at least for the next 10 years.

Also, we will ignore SS and Medicare and state taxes for this spreadsheet to simplify it. However, there are rows in red that we won’t use for the project that have these items. They are there if you want to add them to the spreadsheet after the class for your own personal use.

The spreadsheet has a LOT of comments. They have been put there to answer the majority of the questions that we (my TA and me) get during office hours. Please look to the spreadsheet for answers before you come to me or the TA with questions.

Notes:

1) You’ll need to change row 2 to have the correct years.

2) You’ll need to change row 3 to align with the age for the person you are representing in the spreadsheet. I’d recommend starting the date/age with the first year you are out of college and begin working.

3) Row 9, 10, and 11 of the personal income statement would only occur later in life when you are taking money out of your retirement accounts. These distributions will be taxed as ordinary income.

4) Exemptions have been eliminated in the tax legislation for 2018, however, there are still child credits. Research the appropriate tax rates, credits (google 2019 IRS tax tables, click on Forbes URL, it has a good summary of tax rates).

5) You either take a standard deduction or an itemized deduction in a respective year, not both.

6) If you take the standard deduction you can’t deduct the interest on your house. You can only deduct interest on a home loan and property taxes if you itemize your deductions.

NOTE: We have had issues with students using Google Docs. It is a great collaborative tool, however, when you go to upload to D2L to hand in the assignment, often the formula’s of the spreadsheet are lost. Since we require to see the formulas to grade the project properly this is not acceptable. You may want to use the Microsoft free online version of excel. The benefit is that it seems to download acceptably and the formulas are intact, however, it renders very slowly when working collaboratively among group members. I recommend that you just have the spreadsheet on your local computer work on it and use some revision control and email from member to member to make the necessary edits. This is collaboratively a bigger hassle, but you will get the spreadsheet to download accurately.

INSTRUCTIONS

K

Distribution

9

.5 but must take minimum withdrawl at least at

.5

Distribution

withdrawls from retirement accounts; This will only have a value during retirement; Can take penalty free withdralws at 59.5 but must take minimum withdrawl at least at 70.5

Assume you do not receive social security or medicare for this project, but for your own purposes later on feel free to include these lines

MUST fill in all cells of this color

ried filing jointly)

1K Contributions

Contributions

just for total deduction;Only used for total deductions calculations; Repeat this respective value in residuals; Be mindful of contribution limits

tax changes, Assume $2000 credit per child if income is less than $400K / yr.

Distributions

s

% of annual gross income

for Retirement

00

up to a certain %, not apart of total residual income

negligable, cap at 50%

ly salary

IRA

401K
Roth IRA
Mortgage

Fill out assumptions tab

Subject Line from Personal Finance Statement

Comment

Read through all comments on the assumptions and personal finance statement tabs

Salary

Starting salary after graduation; Need to account for raises

Link assumptions and all amortization tables to the personal finance sheet where appropriate

Spouse Salary

Include spouse salary if filing jointly

Read the PDF also on D2L. It has a lot of hints for completing this successfully.

Company Bonus

optional if you’ll have it

4

0

1

withdrawls from retirement accounts; This will only have a value during retirement; Can take penalty free withdralws at

5

70

All comments from personal finance statement are re-copied here for easier reference. READ THROUGH ALL INSTRUCTION.

Traditional

IRA

Retirement funds from other investments

withdrwal from retirment account;Will want to use this if you retire before age 59.5; This is only retirement funds, if you wish to have extra investment funds accounts for additional income priror to retirement, you need to add rows here in income as well as in residual and currrent asset sections

DO NOT alter cells in this color

Social Security

Assume you do not receive social security or medicare for this project, but for your own purposes later on feel free to include these lines

MUST fill in all cells of this color

Medicare

Gross

Income

Total of all sources of income

Exemptions ( 1 per dependent, enter $)

Red cells are not used for this project but are there for reference if you choose to use this later.

Standard Deduction (Single taxpayer)

Standard Deduction (

Mar

Itemized Deductions

40

just for total deduction;Only used for total deductions calculations; Repeat this respective value in residuals; Be mindful of contribution limits

Traditional IRA

Total Deductions

Total deductions, make sure you are only claiming either standard or itemized deductions, but not both

Taxable Net Income

What is taxable of gross income after deductions

Tax Expense

Change tax brackets as taxable net income changes

Child Tax Credit

Look up

2019

After Tax Income

What’s left of gross income after tax expense; (Gross income – tax expense)

Roth IRA

retirement fund withdrawl

Total After Tax Net Income

Include inflation where necessary, needs to reflect increase in dependents (kids, spouse); Add lines as necessary

Expenses

Rent

Utilities

Food

Formula here only used to illustrate inflation, use your own values

Health Care

To be conservative grow twice inflation. You will still have medical expenses even if covered by company policy; Do not assume medicade or medicare

Travel

Usually understated. You will not travel like a college student the rest of your life

Vehicle

Gas, maintenance, etc. If you chose to include your actual car purchases here, link to auto amortiztaion worksheet or note the entire purchase made in cash

Miscellaneous

Mortgage

take down payment directly out of asset account (make a comment to indicate where) and link annual payments to house mortgate amortization worksheet

Student Loan

Payment

link to student loan amortization worksheet

Other

Total Expenses

Total of all expenses above

Residual Income

What income is left after taxes and annual expenses; (after tax net income – total expenses)

Funds to savings & checking account

Place an IF statement here to aid in capping your savings account at

50

Other Investments

make this the result of all other input to asset account subtracted from the residual income

Funds to IRA

Annual contribution limits between IRA and Roth are

55

Funds to

401K

Annual contribution limit is 18,000

Funds to Roth IRA

Only qualify to deposit while in certain tax brackets

Company Match; not included in residual income

Match to the

401k

Assets

Current Assets

These are cumulative accounts input from the income statement above

Savings & Checking account

Interest

year

Other Investments for Retirement

Select an appropriate rate of return for all retirement accounts.

Total Current Assets

Total of the current asset accounts this year

Fixed Assets

Do not depreciate or appreciate assets through time (with the expection of personal property) value will be dealt with at time of sale (such as trading in for a new car); If you are reading this and all other instructions, make a comment on your savings at age 95 stating where the money goes after you die

Auto

House

This is the total value of the house. This value will remain constant until you sell it. You will need to show the down payment coming from either savings & checking or other investments.

Personal Property

Total Fixed Assets

This grows as you accumulate stuff, not with inflation or interest

Total Assets

current and fixed assets

Liabilities

Current Liabilities (can be zero for this project)

Total Current Liabilities

Long Term Liabilities

respective year cell to the ending year balance of the amortization sheet;Link to the transposed end of year balances in respective amortization worksheet

Student Loan Debt

Auto Debt

First year loan value is equal to the house value minus the down payment

Total Long Term Liabilities

Total Liabilities

current + long term liabilities

Net Worth

Assets – Liabilities

Sign your name to the right if you have red all the comments/instructions listed above.

Assumptions

Other

Rates

1

Other Investments 1

1

Traditional IRA 1
401k 1
Roth IRA 1

0

Fill out all yellow boxes
Ref
		Interest	Reference	Inflation (non-negligible)
Salary rate of raise (outpace inflation)
NOTE: This assumption area will be used by yourself to help you coordinate timing for filling out the personal income statement and balance sheet. It will be used by the grader to help understand your persona.
Who are you?	Notes	Additional info
Profession
Location (can be multiple)
Marriage? (enter y or n)
If yes, spouse profession & year married
Kids? (enter y or n)
If yes to kids, what years?
Do you have student loans? (enter y or n)
What year do you buy a house?
What year(s) do you buy a car? (One must be done with a loan and amortization)
Any large/fun purchases? When/What?
What retirement accounts will you use?
When do you retire?
Any additional notes:

Personal Income Statement

Year Year Year Year Year Year Year Year Year

Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year

22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91

Personal Income Statement
Income
Medicare

Exemptions ( 1 per dependent, enter $)
Standard Deduction (Single taxpayer)
Standard Deduction (Married filing jointly)
Itemized Deductions

Total After Tax Net Income

Rent
Utilities

Admin: Admin:
Show life after retirement including taxes and continuous expenses. Do not have to retire at 55, but you will stop taking in a salary and will start distributing from other retirement accounts. You will no longer pull company match and will not contribute to 401k or traditional IRA once you reach age of 70.5, but do not need to continue to contribute.
Check to make sure that if you had kids as dependents you are removing them from expenses and tax dedcutions as necessary.

Miscellaneous

Other

Katie Kelly: Admin:
Example of an IF statement. This type of statement ensures that if 0 is going into check/savings account that the unused percentage is still going into an asset account.

$ – 0

$ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0 $ – 0

	Name																																																														Year		Year Admin: Admin: Show life after retirement including taxes and continuous expenses. Do not have to retire at 55, but you will stop taking in a salary and will start distributing from other retirement accounts. You will no longer pull company match and will not contribute to 401k or traditional IRA once you reach age of 70.5, but do not need to continue to contribute. Check to make sure that if you had kids as dependents you are removing them from expenses and tax dedcutions as necessary.
	3/18/20			2020			2021	20		22	20		23	20					24	20		25	20		26	20		27	20		28	20		29	20		30	20		31	20		32	20		33	20		34	20		35	20		36	20		37	20		38	20		39		2040	20		41	20		42	20		43	20		44	20		45	20		46	20		47	20		48	20		49		2050	20		51	20		52	20		53	20		54		2055	20		56	20		57	20		58		2059	20		60	20		61	20		62	20		63	20		64	20		65	20		66	20		67	20		68	20		69		2070	20		71	20		72	20		73	20		74	20		75	20		76	20		77	20		78	20		79	20		80	20		81	20		82	20		83	20		84	20		85	20		86	20		87	20		88	20		89	20		90	20		91
	Age		21 Admin: Admin: Input your age here		92
Salary Admin: Starting salary after graduation Need to account for raises
Spouse Salary Admin: Admin: Include spouse salary if filing jointly
Company Bonus Admin: Admin: optional if you’ll have it
401K Distribution Admin: Admin: withdrawls from retirement accounts This will only have a value during retirement Can take penalty free withdralws at 59.5 but must take minimum withdrawl at least at 70.5
Traditional IRA Distribution Admin: Admin:withdrawls from retirement accounts This will only have a value during retirement Can take penalty free withdralws at 59.5 but must take minimum withdrawl at least at 70.5
Retirement funds from other investments Admin: Admin:withdrwal from retirment account Will want to use this if you retire before age 59.5 This is only retirement funds, if you wish to have extra investment funds accounts for additional income priror to retirement, you need to add rows here in income as well as in residual and currrent asset sections
Social Security Admin: Admin: Assume you do not receive social security or medicare for this project, but for your own purposes later on feel free to include these lines
Gross Income Admin: Admin: Total of all sources of income
401K Contributions Admin: Admin:just for total deduction Only used for total deductions calculations Repeat this respective value in residuals Be mindful of contribution limits
Traditional IRA Contributions Admin: Admin: just for total deduction Only used for total deductions calculations Repeat this respective value in residuals Be mindful of contribution limits
Total Deductions Admin: Admin: Total deductions, make sure you are only claiming either standard or itemized deductions, but not both
Taxable Net Income Admin: What is taxable of gross income after deductions
Tax Expense Admin: Admin: Change tax brackets as taxable net income changes
Child Tax Credit Zignego, Bruce: Zignego, Bruce: Lood up 2018 tax changes, Assume $2000 credit per child if income is less than $400K / yr.
After Tax Income Admin: Admin: What’s left of gross income after tax expense (Gross income – tax expense)
Roth IRA Distributions (do not move this above taxes) Admin: Admin: DO NOT USE UNTIL RETIREMENT AT 55 OR ALTER retirement fund withdrawl
Expenses Admin: Admin: Include inflation where necessary, needs to reflect increase in dependents (kids, spouse) Add lines as necesary
Food Admin: Admin: Formula here only used to illustrate inflation, use your own values	$2,000	$2,000 Admin: Admin: Use this equation to map inflation over time. Substitute proper values for each expense and chose your own rate of inflation.
Health Care Admin: Admin: To be conservative grow twice inflation. You will still have medical expenses even if covered by company policy Do not assume medicade or medicare
Admin: Admin: Use this equation to map inflation over time. Substitute proper values for each expense and chose your own rate of inflation.	Travel Admin: Admin: Usually understated. You will not travel like a college student the rest of your life
Vehicle Admin: Admin: Gas, maintenance, etc. If you chose to include your actual car purchases here, link to auto amortiztaion worksheet or note the entire purchase made in cash
Mortgage Admin: Admin: take down payment directly out of asset account (make a comment to indicate where) and link annual payments to house mortgate amortization worksheet
Student Loan Payments Admin: Admin: link to student loan amortization worksheet
Total Expenses Admin: Admin: Total of all expenses above
Residual Income Admin: Admin: What income is left after taxes and annual expenses (after tax net income – total expenses)
Admin: Admin: Spend all the residual income by distributing into asset accounts	Funds to savings & checking account Admin: Admin: Place an IF statement here to aid in capping your savings account at 50% of annual gross income
Other Investments for Retirement Admin: make this the result of all other input to asset account subtracted from the residual income																																																																										$ – 0
Funds to IRA Admin: Admin: Annual contribution limits between IRA and Roth are 5500
Funds to 401K Admin: Admin: Annual contribution limit is 18,000			$0
Funds to Roth IRA Admin: Admin: Only qualitfy to deposit while in certain tax brackets
Company Match; not included in residual income Admin: Admin: Match to the 401k up to a certain %, not apart of total residual income
Have you spent all your money? (This should be equal to 0 every year)

Personal Balance Sheet

Name Year Year Year Year Year Year Year Year Year Year Year
Admin: Admin:
Show life after retirement including taxes and continuous expenses. Do not have to retire at 55, but you will stop taking in a salary and will start distributing from other retirement accounts. You will no longer pull company match and will not contribute to 401k or traditional IRA once you reach age of 70.5, but do not need to continue to contribute.
Check to make sure that if you had kids as dependents you are removing them from expenses and tax dedcutions as necessary. Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year Year
3/18/20 2020 2021

2040

2050

2055

2059

2070

Age 21
Admin: Admin:
Input your age here 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 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

Assets

$0

Other Investments for Retirement

401K $0

Roth IRA

Auto

House

Liabilities
Current Liabilities (can be zero for this project)

Total Current Liabilities

Student Loan Debt
Auto Debt

Mortgage

Total Long Term Liabilities

	2022			2023	2024	2025	2026	2027	2028	2029	2030	2031	2032	2033	2034	2035	2036	2037	2038	2039	2041	2042	2043	2044	2045	2046	2047	2048	2049	2051	2052	2053	2054	2056	2057	2058	2060	2061	2062	2063	2064	2065	2066	2067	2068	2069	2071	2072	2073	2074	2075	2076	2077	2078	2079	2080	2081	2082	2083	2084	2085	2086	2087	2088	2089	2090	2091
Personal Finance Sheet
Current Assets Admin: Admin: These are cumulative accounts input from the income statement above
Savings & Checking account Admin: Admin: Iterest negligable, cap at 50% yearly salary	$0 Admin: Admin: This kind of equation accoutns ffor current assets in the account plus this years deposit. This is cumulative.
Traditional IRA Admin: Admin: Select an appropriate rate of return for all retirement accounts.
$0 Admin: Admin: This equation accounts for growth of the asset from last year plus the deposit from the current year and also accounts for when retirement funds are used in gross income. Use your own interest rates
Total Current Assets Admin: Admin: Total of the current asset accounts this year
Fixed Assets Admin: Admin: Do not depreciate or appreciate assets throughtime (with the expection of personal property) value will be dealt with at time of sale (such as trading in for a new car) If you are reading this and all other instructions, make a comment on your savings at age 95 stating where the money goes after you die
Personal Property Admin: Admin: This grows as you accumulate stuff, not with inflation or interest
Total Fixed Assets Admin: Admin: Sum of fixed assets for the current year
Total Assets Admin: Admin: current and fixed assets
Long Term Liabilities Admin: Admin: respective year cell to the ending year balance of the amortization sheet. Link to the transposed end of year balances in respective amortization worksheet
Total Liabilities Admin: Admin: current + long term liabilities
Admin: Admin: Input your age here	Net Worth Admin: Admin: Assets – Liabilities
Admin: Admin: These are cumulative accounts input from the income statement above	Admin: Admin: Iterest negligable, cap at 50% yearly salary	Admin: Admin: This kind of equation accoutns ffor current assets in the account plus this years deposit. This is cumulative.	Admin: Admin: Select an appropriate rate of return for all retirement accounts.	Admin: Admin: This equation accounts for growth of the asset from last year plus the deposit from the current year and also accounts for when retirement funds are used in gross income. Use your own interest rates	Admin: Admin: Total of the current asset accounts this year	Admin: Admin: Do not depreciate or appreciate assets throughtime (with the expection of personal property) value will be dealt with at time of sale (such as trading in for a new car) If you are reading this and all other instructions, make a comment on your savings at age 95 stating where the money goes after you die	Admin: Admin: This grows as you accumulate stuff, not with inflation or interest	Admin: Admin: Sum of fixed assets for the current year	Admin: Admin: current and fixed assets	Admin: Admin: respective year cell to the ending year balance of the amortization sheet. Link to the transposed end of year balances in respective amortization worksheet

Mortgage Amortization

Mortgage Amortization

ly payments

Year Month

Interest

0

Mar
Year Beginning Balance Payment Principal Interest Cumulative Principal Cumulative Interest Ending Balance

Jan 1.00 Jan

Feb 2.00 Feb

Mar 3.00 Mar

Apr 4.00 Apr

May 5.00 May

Jun 6.00 Jun

Jul 7.00 Jul

Aug 8.00 Aug

Sep 9.00 Sep

Oct

Oct

Nov 11.00 Nov

Dec 12.00 Dec

24

)

0.00 0.00

0.00 0.00

0.00 0.00 Months 0.00
0.00 0.00

0

0.00 0.00

0.00

0.00 0.00

0.00 0.00

12.00

0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00

Inputs

Key Figures

Loan principal amount

Annual loan payments

Annual interest rate

Month

Loan period in years

Interest in first calendar year

Base year of loan

Interest over term of loan

Base month of loan

Sum of all payments

Payments in First 12

Months

Beginning Balance

Payment

Principal

Cumulative Principal

Cumulative Interest

Ending Balance

Jan

Feb

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

Yearly Schedule of Balances and Payments

Transposed End of Year Balance (use this to link to long term debt)

DO NOT ERASE

1.00

2.00

3.00

4.00

5.00

6.00

7.00

8.00

9.00

1

0.00

10.00

11.00

12.00

(

24.00

Base Year

Years

Last Year

Mos in Last Yr

Car Amortization

Car Amortization

Inputs Key Figures

Loan principal amount

Annual loan payments

Annual interest rate

Loan period in years 5 Interest in first calendar year

Base year of loan 2019 Interest over term of loan

Base month of loan Sum of all payments

Year Month Beginning Balance Payment

Interest Cumulative Principal Cumulative Interest Ending Balance

2019 Jan $100,000.00 $1,887.12

$1,470.45 $416.67

Feb $98,529.55 $1,887.12

Mar $97,052.97 $1,887.12

Apr $95,570.24 $1,887.12

May $94,081.33 $1,887.12

.82

Jun $92,586.22 $1,887.12

Jul $91,084.88 $1,887.12

Aug $89,577.28 $1,887.12

Sep $88,063.40 $1,887.12

Oct $86,543.21 $1,887.12

Nov $85,016.69 $1,887.12

Dec $83,483.81 $1,887.12

$4,589.98

Yearly Schedule of Balances and Payments Transposed End of Year Balance (use this to link to long term debt)
Year Beginning Balance Payment Principal Interest Cumulative Principal Cumulative Interest Ending Balance year

2,021

2020 $81,944.54 $22,645.44

.08

62,965

0

2021

$22,645.44

2022 $43,014.82 $22,645.44

2023 $22,043.87 $22,645.44 $22,043.87

$100,000.00 $13,227.20

DO NOT ERASE

1.00 Jan 1.00 Jan
2.00 Feb 2.00 Feb
3.00 Mar 3.00 Mar
4.00 Apr 4.00 Apr
5.00 May 5.00 May
6.00 Jun 6.00 Jun
7.00 Jul 7.00 Jul
8.00 Aug 8.00 Aug
9.00 Sep 9.00 Sep
10.00 Oct 10.00 Oct
11.00 Nov 11.00 Nov
12.00 Dec 12.00 Dec

24

36.00 24.00

12.00

0.00 Months 60.00

60.00 0.00 Base Year 2019
60.00 0.00 Years 5.00
60.00 0.00 Last Year 2023
60.00 0.00 Mos in Last Yr 12.00
60.00 0.00

60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00
60.00 0.00

		$100,000.00					$22,645.44
5.000%		Monthly payments													$1,887.12
	$4,589.98
	$13,227.20
$113,227.20
	Payments in First 12 Months		Principal
	$1,470.45		$416.67		$98,529.55
$1,476.58	$410.54	$2,947.03	$827.21		$97,052.97
$1,482.73	$404.39	$4,429.76	$1,231.60		$95,570.24
$1,488.91	$398.21	$5,918.67	$1,629.81		$94,081.33
$1,495.11	$392.01	$7,413.78	$	2,021		$92,586.22
$1,501.34	$385.78	$8,915.12	$2,407.60		$91,084.88
$1,507.60	$379.52	$10,422.72	$2,787.12		$89,577.28
$1,513.88	$373.24	$11,936.60	$3,160.36		$88,063.40
$1,520.19	$366.93	$13,456.79	$3,527.29		$86,543.21
$1,526.52	$360.60	$14,983.31	$3,887.89		$85,016.69
$1,532.88	$354.24	$16,516.19	$4,242.13		$83,483.81
$1,539.27	$347.85	$18,055.46		$81,944.54
2,020 tc={709D8048-B310-452D-BD56-0C87D7DB1819}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Do not change formulas in these cells unless loan period is longer than 7 years. May need to extend the table if loan period is longer than 7 years.	2,022	2,023
$18,979.46	$3,665.98	$37,034.92	$8,255.96	$	62,965	43,015	22,044
$62,965.08	$19,950.26	$2,695.18	$56,985.18	$10,951.14		$43,014.82
$20,970.95	$1,674.49	$77,956.13	$12,625.63			$22,043.87
$601.57	$0.00
	36.00
48.00
																											60.00

Student Loan Amortization

Student Loan Amortization

Inputs Key Figures
Loan principal amount Annual loan payments
Annual interest rate Monthly payments
Loan period in years Interest in first calendar year
Base year of loan Interest over term of loan
Base month of loan Sum of all payments

Payments in First 12 Months
Year Month Beginning Balance Payment Principal Interest Cumulative Principal Cumulative Interest Ending Balance
0 Jan
Feb
Mar
Apr
May
Jun
Jul
Aug

Sep Transposed End of Year Balance (use this to link to long term debt)
Oct year

Nov

Dec

Yearly Schedule of Balances and Payments

Year Beginning Balance Payment Principal Interest Cumulative Principal Cumulative Interest Ending Balance
DO NOT ERASE
1.00 Jan 1.00 Jan
2.00 Feb 2.00 Feb
3.00 Mar 3.00 Mar
4.00 Apr 4.00 Apr
5.00 May 5.00 May
6.00 Jun 6.00 Jun
7.00 Jul 7.00 Jul
8.00 Aug 8.00 Aug
9.00 Sep 9.00 Sep
10.00 Oct 10.00 Oct
11.00 Nov 11.00 Nov
12.00 Dec 12.00 Dec
24

0.00 0.00

0.00 0.00

0.00 0.00 Months 0.00
0.00 0.00 Base Year 0
0.00 0.00 Years 0.00
0.00 0.00 Last Year
0.00 0.00 Mos in Last Yr 12.00

0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00
0.00 0.00

tc={DACC34CB-DD5B-4336-BBEA-770E28D82438}: [Threaded comment] Your version of Excel allows you to read this threaded comment; however, any edits to it will get removed if the file is opened in a newer version of Excel. Learn more: https://go.microsoft.com/fwlink/?linkid=870924 Comment: Do not change formulas in this cell unless your loan period is longer than 11 years. May need to extend table if loan period is longer than 11 years.

(24.00)

Sheet3