Now we need to begin working on the worksheet 2. Let's begin by renaming sheet 2 to Loan Data.
- Use the VLOOKUP function to determine the Interest Rate & PMT function to determine the monthly payment.
Column E: Interest Rate using VLOOKUP
Column F: Down Payment = VLOOKUP to determine % Required for Down Payment * Selling Price
Column G: Amount Financed = Selling Price - Down Payment
Column H: Monthly Payment = PMT function (Monthly payment is at the beginning of each period)
Cells C41 - C44: Use functions COUNT, MIN, MAX & SUM to complete these loan statistics
- Format the data on this sheet for currency, percent, and so forth.
- Format the title rows and center them across the column selection. Format the column headings appropriately.
- Format the Loan Term Data and Loan Statistics with a clean outside border, color formatting, currency, and percent.
An example of what your Loan Data worksheet might look like is below.
A E C E G H The Condos at Summit Ridge Mountain We sell your condo at the price you set! 1 Custome Selling Agent Selling Price Loan Term Interest Rat Down Paymer Amount to be Financ Monthly Payment : 3 Jones Gonzales 645,250.00 30 4 Curp Aiello Langston Kloster 465,000.00 30 5 215,000.00 20 6 7Arnold Goodrich 106,000.00 15 8 Harris Slaughte Pinter Dyer Graham Minkus Minkus 156,750.00 382,500.00 20 9 Minkus 339,999.00 15 10 308,750.00 30 11 Pinderga Gonzales 13 Morgan Pinkston 14 Rodgers Langston 302,250.00 30 12 # 93,100.00 15 397,700.00 20 255,000.00 Fritz 16 Collins Singleton Barboza Kloster Qualls Gonzales 20 15 179,000.00 30 75,900.00 15 17 Gonzales 382,500.00 20 18 Pinkston 20 Young Langston Weger Singleton 22 Burnes Haley 23 Smith Goodrich 24 Thomas Langston Yates $ 799,000.00 30 19 598,000.00 30 291,400.00 30 21 150,000.00 15 $ 276,450.00 20 163,709.00 20 25 Stone Pinter $ 182,304.00 20 Haley Oliver 246,905.00 26 27 Morris 28 Miranda Kloster 29 Medley Minkus Minkus 186,102.00 15 264,000.00 30 86,330.00 15 236,685.00 30 Lind 31 King 32 Jordan Haley 33 Hall Gonzales 30 Langston 565,000.00 30 159,264.00 20 Singleton $ # 233,605.00 20 34 Loan Term Required for Down Payment Interest Rate 35 15 0.0575 0.15 36 20 0.06 0.2 37 30 0.0625 0.25 38 Loan Statistics 39 Number of Loans Highest Amount Financed 40 41 Lowest Amount Financed 42 Total Amount Financed 43 44 Todays Date 45 The Condos at Summit Ridge Mountain We sell your condo at the price you set! Monthly Interest Loan Amount to be Customer Selling Agent Selling Price Financed Term Rate Down Payment Payment 6.25% $ 161,312.50 $ 6.25% $ 116,250.00 $ $2,964.25 Gonzales 30 Jones 645,250.00 483,937.50 $ $2,136.19 $1,226.13 $744.63 $1,101.14 $2,181.37 $2,388.43 $1,418.38 $1,388.52 $654.01 348,750.00 Curp Langston 465,000.00 30 6.00% $ Kloster Aiello 43,000.00 $ 215,000.00 20 172,000.00 5.75% $ $ Arnold Goodrich 15,900.00 $ 106,000.00 5 90,100.00 5.75% $ Harris Minkus 23,512.50 $ 156,750.00 15 133,237.50 Slaughter Pinter 6.00% $ 76,500.00 $ 382,500.00 20 306,000.00 5.75% $ Minkus 50,999.85 $ Dyer 339,999.00 15 288,999.15 6.25% $ Minkus $ Graham ,187.50 $ 308,750.00 30 231,562.50 Pindergast Gonzales E 15 75,562.50 $ 302,250.00 6.25% $ 226,687.50 Pinkston $ 5.75% $ 13,965.00 $ Morgan 93,100.00 79,135.00 6.00% $ $2,268.06 $1,454.25 Rodgers Langston 397,700.00 79,540.00 $ 318,160.00 20 6.00% $ Fritz $ Gonzales 255,000.00 20 51,000.00 $ 204,000.00 6.25% $ Collins $822.32 Singleton 44,750.00 $ 134,250.00 179,000.00 30 5.75% $ $533.18 $ Barboza Kloster 11,385.00 $ 64,515.00 75,900.00 Qualls Gonzales 6.00% $ 6.25% $ 199,750.00 $ 6.25% $ 149,500.00 $ 76,500.00 $ $2,181.37 $3,670.57 $2,747.18 $1,338.68 $1,053.72 $1,576.58 382,500.00 306,000.00 20 Pinkston $ 599,250.00 Yates 799,000.00 30 598,000.00 $ 30 Young Langston 448,500.00 6.25% $ Singleton 30 72,850.00 $ Weger 291,400.00 218,550.00 22,500.00 $ 55,290.00 $ 5.75% $ Hakey Burnes 150,000.00 15 127,500.00 6.00% $ $ 276,450.00 Smith Goodrich 20 221,160.00 6.00% $ $933.62 Thomas 32,741.80 $ Langston 163,709.00 20 130,967.20 6.00% $ 145,843.20 $1,039.67 $1,734.46 $1,307.33 $1,212.80 Pinter 36,460.80 $ Stone 182,304.00 20 $ 5.75% $ Oliver Hakey 37,035.75 $ 246,905.00 15 209,869.25 Morris 5.75% $ Minkus 27,915.30 $ 158,186.70 186,102.00 15 $ Miranda Kkoster 30 6.25% $ 66,000.00 $ 264,000.00 198,000.00 5.75% $ 12,949.50 $ $606.45 Medley Minkus 86,330.00 15 73,380.50 $ 6.25% $ $1,087.32 $2,595.58 Lind Gonzakes 236,685.00 30 59,171.25 $ 177513.75 6.25% $ 141,250.00 $ $ King 565,000.00 Langston 30 423,750.00 6.00% $ 6.00% $ $908.27 Jordan Hakey 31,852.80 $ 159,264.00 20 127,411.20 $ 46,721.00 $ $1,332.23 Hall Singleton 233,605.00 20 186,884.00 % Required for Loan Term Interest Rate Down Payment 5.75% 15% 15 6.00% 20% 20 6.25% 25% 30 Loan Statistics Number of Loans 30 Highest Amount Financed $599,250.00 Lowest Amunt Financed $ 64,515.00 6,834,099.95 Total Amount Financed A E C E G H The Condos at Summit Ridge Mountain We sell your condo at the price you set! 1 Custome Selling Agent Selling Price Loan Term Interest Rat Down Paymer Amount to be Financ Monthly Payment : 3 Jones Gonzales 645,250.00 30 4 Curp Aiello Langston Kloster 465,000.00 30 5 215,000.00 20 6 7Arnold Goodrich 106,000.00 15 8 Harris Slaughte Pinter Dyer Graham Minkus Minkus 156,750.00 382,500.00 20 9 Minkus 339,999.00 15 10 308,750.00 30 11 Pinderga Gonzales 13 Morgan Pinkston 14 Rodgers Langston 302,250.00 30 12 # 93,100.00 15 397,700.00 20 255,000.00 Fritz 16 Collins Singleton Barboza Kloster Qualls Gonzales 20 15 179,000.00 30 75,900.00 15 17 Gonzales 382,500.00 20 18 Pinkston 20 Young Langston Weger Singleton 22 Burnes Haley 23 Smith Goodrich 24 Thomas Langston Yates $ 799,000.00 30 19 598,000.00 30 291,400.00 30 21 150,000.00 15 $ 276,450.00 20 163,709.00 20 25 Stone Pinter $ 182,304.00 20 Haley Oliver 246,905.00 26 27 Morris 28 Miranda Kloster 29 Medley Minkus Minkus 186,102.00 15 264,000.00 30 86,330.00 15 236,685.00 30 Lind 31 King 32 Jordan Haley 33 Hall Gonzales 30 Langston 565,000.00 30 159,264.00 20 Singleton $ # 233,605.00 20 34 Loan Term Required for Down Payment Interest Rate 35 15 0.0575 0.15 36 20 0.06 0.2 37 30 0.0625 0.25 38 Loan Statistics 39 Number of Loans Highest Amount Financed 40 41 Lowest Amount Financed 42 Total Amount Financed 43 44 Todays Date 45 The Condos at Summit Ridge Mountain We sell your condo at the price you set! Monthly Interest Loan Amount to be Customer Selling Agent Selling Price Financed Term Rate Down Payment Payment 6.25% $ 161,312.50 $ 6.25% $ 116,250.00 $ $2,964.25 Gonzales 30 Jones 645,250.00 483,937.50 $ $2,136.19 $1,226.13 $744.63 $1,101.14 $2,181.37 $2,388.43 $1,418.38 $1,388.52 $654.01 348,750.00 Curp Langston 465,000.00 30 6.00% $ Kloster Aiello 43,000.00 $ 215,000.00 20 172,000.00 5.75% $ $ Arnold Goodrich 15,900.00 $ 106,000.00 5 90,100.00 5.75% $ Harris Minkus 23,512.50 $ 156,750.00 15 133,237.50 Slaughter Pinter 6.00% $ 76,500.00 $ 382,500.00 20 306,000.00 5.75% $ Minkus 50,999.85 $ Dyer 339,999.00 15 288,999.15 6.25% $ Minkus $ Graham ,187.50 $ 308,750.00 30 231,562.50 Pindergast Gonzales E 15 75,562.50 $ 302,250.00 6.25% $ 226,687.50 Pinkston $ 5.75% $ 13,965.00 $ Morgan 93,100.00 79,135.00 6.00% $ $2,268.06 $1,454.25 Rodgers Langston 397,700.00 79,540.00 $ 318,160.00 20 6.00% $ Fritz $ Gonzales 255,000.00 20 51,000.00 $ 204,000.00 6.25% $ Collins $822.32 Singleton 44,750.00 $ 134,250.00 179,000.00 30 5.75% $ $533.18 $ Barboza Kloster 11,385.00 $ 64,515.00 75,900.00 Qualls Gonzales 6.00% $ 6.25% $ 199,750.00 $ 6.25% $ 149,500.00 $ 76,500.00 $ $2,181.37 $3,670.57 $2,747.18 $1,338.68 $1,053.72 $1,576.58 382,500.00 306,000.00 20 Pinkston $ 599,250.00 Yates 799,000.00 30 598,000.00 $ 30 Young Langston 448,500.00 6.25% $ Singleton 30 72,850.00 $ Weger 291,400.00 218,550.00 22,500.00 $ 55,290.00 $ 5.75% $ Hakey Burnes 150,000.00 15 127,500.00 6.00% $ $ 276,450.00 Smith Goodrich 20 221,160.00 6.00% $ $933.62 Thomas 32,741.80 $ Langston 163,709.00 20 130,967.20 6.00% $ 145,843.20 $1,039.67 $1,734.46 $1,307.33 $1,212.80 Pinter 36,460.80 $ Stone 182,304.00 20 $ 5.75% $ Oliver Hakey 37,035.75 $ 246,905.00 15 209,869.25 Morris 5.75% $ Minkus 27,915.30 $ 158,186.70 186,102.00 15 $ Miranda Kkoster 30 6.25% $ 66,000.00 $ 264,000.00 198,000.00 5.75% $ 12,949.50 $ $606.45 Medley Minkus 86,330.00 15 73,380.50 $ 6.25% $ $1,087.32 $2,595.58 Lind Gonzakes 236,685.00 30 59,171.25 $ 177513.75 6.25% $ 141,250.00 $ $ King 565,000.00 Langston 30 423,750.00 6.00% $ 6.00% $ $908.27 Jordan Hakey 31,852.80 $ 159,264.00 20 127,411.20 $ 46,721.00 $ $1,332.23 Hall Singleton 233,605.00 20 186,884.00 % Required for Loan Term Interest Rate Down Payment 5.75% 15% 15 6.00% 20% 20 6.25% 25% 30 Loan Statistics Number of Loans 30 Highest Amount Financed $599,250.00 Lowest Amunt Financed $ 64,515.00 6,834,099.95 Total Amount Financed
