Question

i need help working out the problem and entering it into excel

Background: On January 1, 2020, the company has purchased a 14 year $100,000 bonds investment. The bond calls for an annual payment of interest on 12/31 at a contractual (stated) rate of 6%. Given the credit standing of the issuing company, an interest rate of 8.25% has been imputed as the effective rate. The principal amount of the bond is due at maturity. The company classified this bond investment as Held-to-Maturity. 1. At what amount should the investment be recorded on 1/1/20 (i.e., what is the present value of future cash collection for the company)? Enter the Excel formula (not 5 numbers) in cell B8 in Worksheet. 7 Note: Microsoft Excel's pv function is recommended (there are multiple ways!) 8 9 The function is "=PV( rate,nper,pmt,fv)", where 10 rate = effective interest rate, 11 nper = number of periods. 72 pmt = periodic payments such as annuity during the period, 13 fv = future one time cash amount such as maturity amount.

Answer 1

Solution:

SUM D X fi =PV(A5,A6,A3*A4,A3) В 1 1. Issuance of Bonds Payable Data Description $100,000 Face value on Bonds payable 6.00% Stated interest Rate 8.25% Effective Interest Rate 14 Life of Bond 8 PV of future cash flows =PV(A5,A6,A3* A4,A3)

when we press Enter after putting formula in cell B8, we get ($81,716.82). This is the amount at which investment should be recorded.

2.

Date Cash received Interest Revenue Discount Amortization Dicount Balance Bonds' Carrying value (8.25% of previous yr. Carrying value) (A) (A-B) (6.00% of Face value) (B) Date of Issurance 12/31/2020 $6,000.00 12/31/2021 $6,000.00 12/31/2022 $6,000.00 12/31/2023 $6,000.00 12/31/2024 $6,000.00 12/31/2025 $6,000.00 12/31/2026 $6,000.00 12/31/2027 $6,000.00 12/31/2028 $6,000.00 12/31/2029 $6,000.00 12/31/2030 $6,000.00 12/31/2031 $6,000.00 12/31/2032 $6,000.00 12/31/2033 $6,000.00 $6,741.64 $6,802.82 $6,869.06 $6,940.75 $7,018.36 $7,102.38 $7,193.33 $7,291.78 $7,398.35 $7,513.71 $7,638.59 $7,773.78 $7,920.11 $8,078.52 $741.64 $802.82 $869.06 $940.75 $1,018.36 $1,102.38 $1,193.33 $1,291.78 $1,398.35 $1,513.71 $1,638.59 $1,773.78 $1,920.11 $2,078.52 $18,283.18 $17,541.54 $16,738.72 $15,869.66 $14,928.91 $13,910.55 $12,808.17 $11,614.84 $10,323.06 $8,924.72 $7,411.01 $5,772.41 $3,998.64 $2,078.53 $0.00 (Face value - Disc. Bal) $81,716.82 $82,458.46 $83,261.28 $84,130.34 $85,071.09 $86,089.45 $87,191.83 $88,385.16 $89,676.94 $91,075.28 $92,588.99 $94,227.59 $96,001.36 $97,921.47 $100,000.00

Note: If u have any query, ask me in the comment section. Provide vote ups(ratings) please!!!