Question

PLE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ADD-INS QuickBooks 614 X f r --PMT(4010/12,011*09,08) HT Name: kelly gamer Your score On January 1, 2020 the Kelly Gamer Company borrows $150,000 cash by signing a 5-year, 10%, installment note, with semiannual interest payments. 1) Calculate the amount of each payment using the PMT function. 2) Prepare the amortization schedule for the loan. Enter a valid Excel formula or function in each of the yellow cells below. Formulas must refer to cell address(es). Entering a value will be marked as incorrect $150.000 Your PMT formula is incorrect Loan amount Term (years) Annual interest Payments per year 1) What is the amount of each semiannual loan payment? The PMT function must be used, and formula must return a positive value. 2) Complete the amortization schedule for this note. Al formulas must return a positive value Semiannual Interest Period Beginning Balance $150,000.00 Credit Cash Debit interest Expense Debt Notes Pavable Ending Balance tucions Loans PMT Mortgage Car PMT Limited - 00140 - $74.20 Periode 20120 - $5300 C

Answer 1

Please give positive ratings so I can keep answering. It would help me a lot. Please comment if you have any query. Thanks!

Kelly Garner
For PMT formula in excel has we need five inputs. They are below:
Rate of interest
It is 10% but as amount is paid semiannually so interest rate will half of 10% i.e. 5%
NPER
It is the number of payments. The amount is paid semiannually for 5 years so it is 5*2= 10.
PV
It is the present value i.e. loan amount= $ 150,000.
FV
It is the Future value. After 5 years loan will be paid in full so it should be zero.
Type
It means when the payment is to be made at the beginning or at the end of the month. In our case it should be at the end of the month so it should also be zero.
So PMT= (rate of interest, nper, pv, fv, type)
Rate of interest	5%
NPER	10
PV	150,000.00
FV	0
Type	0
So PMT= (5%, 10, -150000,0,0)
PMT=	$19,425.69
The answer will be negative because it is inbuilt feature of excel that if there is cash outflow then formula will automatically give negative amount. So to get positive value please put loan amount as negative figure like I have done in my solution.
Answer 1
The amount of each semiannual payment is $ 19,425.69
Answer 2
Amortization Schedule	A	C=A*5%	E=D-C	D= PMT	F=A=E
Semiannual Interest period	Beginning balance	Debit Interest Expense	Debit Notes Payable	Credit cash	Ending Balance
1	150,000.00	7,500.00	$11,925.69	$19,425.69	138,074.31
2	138,074.31	6,903.72	$12,521.97	$19,425.69	125,552.34
3	125,552.34	6,277.62	$13,148.07	$19,425.69	112,404.27
4	112,404.27	5,620.21	$13,805.47	$19,425.69	98,598.80
5	98,598.80	4,929.94	$14,495.75	$19,425.69	84,103.06
6	84,103.06	4,205.15	$15,220.53	$19,425.69	68,882.52
7	68,882.52	3,444.13	$15,981.56	$19,425.69	52,900.96
8	52,900.96	2,645.05	$16,780.64	$19,425.69	36,120.32
9	36,120.32	1,806.02	$17,619.67	$19,425.69	18,500.65
10	18,500.65	925.03	$18,500.65	$19,425.69	0.00