N Click vào OK để cố gắng xác định vị trí các tài liệu tham khảo Thông tư. N Click Cancel để nhập công thức như vậy. N Click vào Trợ giúp đểBạn có thể sử dụng công thức mảng bổ sung để tính toán các biện pháp khác cho các dữ liệu trong ví dụ này. | Chapter 11 Introducing Financial Formulas 309 EXAMPLE 20 What are the payments on a loan of 200 000 over 10 years at interest per month with payments in arrears This example is illustrated in Figure 11-4. Figure 11-4 Calculating a loan payment Function required PMT rate nper pv fv type The following formula returns 2 PMT 120 200000 0 0 This result can be verified by using the PV function to calculate the loan amount. The following formula returns 200 000 PV 120 0 0 In this example the loan is fully repaid after 10 years and the fv argument is zero. Also note that the payments are to be monthly and the monthly loan rate has been quoted. Therefore the 10-year term is converted to months. EXAMPLE 21 I can afford payments of 2 500 per month and can borrow at per month over 20 years. How much can I afford to borrow on a fully redeemable mortgage Function required PV rate nper pmt fv type This formula returns 366 PV 240 -2500 0 0 310 Part 111 Financial Formulas Note that with mortgages we always assume payments are in arrears and that the type argument is 0. Also note that the rate of interest and the payments are monthly. Therefore the term of 20 years must be converted to months. You can check the answer by using the calculated answer to determine the rate on a mortgage of 366 over 240 months. The following formula returns RATE 240 -2500 0 0 EXAMPLE 22 I currently owe 150 000 on a mortgage and make payments of 1 900 per month. The current interest rate is per month. How long will it take to repay the loan Function required NPER rate pmt pv fv type The following formula returns NPER -1900 150000 0 0 Because interest and payments are monthly the formula returns the amortization period in months. This answer although correct in mathematical terms has a practical implication. Payments are actually made on exact monthly anniversaries. This calculation implies that the loan somehow gets repaid
