Tài chính ngân hàng - Lập mô hình ngân lưu nợ vay trên excel

Nội dung text: Tài chính ngân hàng - Lập mô hình ngân lưu nợ vay trên excel

1. CHƯƠNG TRÌNH GIẢNG DẠY KINH TẾ FULBRIGHT FULBRIGHT ECONOMICS TEACHING PROGRAM 16 tháng 02 năm 2011 Chú giải LẬP MÔ HÌNH NGÂN LƯU NỢ VAY TRÊN EXCEL Bài chú giải này hướng dẫn học viên xây dựng mô hình ngân lưu nợ vay trên Excel. Chúng ta bắt đầu bằng cách khởi động Excel với một tập tin được tạo mới theo mặc định. Đổi tên bảng tính thứ nhất “Sheet1” thành “Model” và bảng tính thứ hai “Sheet2” thành “Rate”. Ở bảng tính “Model”, nhập tựa đề “MÔ HÌNH NGÂN LƯU NỢ VAY” vào ô A1. Nhập thông tin thời gian và đơn vị Khung thông tin đầu vào thứ nhất trong mô hình tài chính là thời gian và đơn vị tiền tệ. Nhập tựa đề “THỜI GIAN VÀ ĐƠN VỊ” vào ô A3 và định dạng khung cho diện tích A3:B8. Sau đó, từ ô A4 đến A8, nhập lần lượt: “Ngày giải ngân”, “Ngày thanh toán thứ nhất”, “Hệ đếm ngày”, “Tần suất thanh toán” và “Đơn vị tiền tệ”. Cột B tương ứng trong khung là các giá trị cần nhập. Ô B4 và B5 là các giá trị thời gian được định dạng dd/mm/yyyy (tức là ngày với 2 chữ số/tháng với 2 chữ số/năm với 4 chữ số). Toàn bộ khoản vay được giải ngân một lần vào ngày 8/3/2010. Sau ngày này, lãi bắt đầu được tính trên dư nợ. Vậy, ta nhập 3/8/2011 vào ô 2011. 1 Ngày trả nợ gốc và lãi là mùng 1 hàng tháng. Ta nhập 4/1/2011, ngày trả nợ đầu tiên, vào ô B5. Hệ đếm ngày là quy ước đếm ngày mà các tổ chức và thị trường tài chính sử dụng để tính lãi. Hệ đếm ngày 30/360 quy ước một tháng có đúng 30 ngày và một năm có đúng 360 ngày. Đây là hệ thông dụng nhất được các ngân hàng áp dụng. Hệ đếm ngày actual/360 tính số ngày thực tế theo lịch, nhưng vẫn quy ước một năm có 360 ngày. Hệ đếm ngày actual/365 tính số ngày thực tế theo lịch và quy ước một năm có 365 ngày. Ở ô B6, ta nhập giá trị 1 nếu sử dụng hệ đếm ngày 30/360, 2 nếu sử dụng actual/360 và 3 nếu là actual/365. Ví dụ, ta đếm số ngày tính lãi giữa ngày giải ngân (8/3/2011) và ngày thanh toán thứ nhất (1/4/2011). Với hệ 30/360, tháng 3 được quy ước là có 30 ngày nên ta đếm 23 ngày từ 8/3 đến 1/4. Với hệ actual/360 và actual/365, tháng 3 thực tế có 31 ngày nên ta đếm 24 ngày từ 8/3 đến 1/4. 1 Định dạng ngày tháng mặc định của Excel là tháng/ngày/năm, nên ta phải nhập 3/8/2011 vào ô 2011 để hiện thị giá trị 08/03/2011. Tình huống này do Nguyễn Xuân Thành soạn. Các nghiên cứu tình huống của Chương trình Giảng dạy Fulbright được sử dụng làm tài liệu cho thảo luận trên lớp học, chứ không phải là để ủng hộ, phê bình hay dùng làm nguồn số liệu cho một tình huống chính sách cụ thể.
2. Vì lãi suất thường được niêm yết theo năm nên để tính lãi ta phải tính hệ số ngày bằng cách chia số ngày đếm được cho tổng số ngày trong năm. Hệ số ngày từ 8/3 đến 1/4 theo hệ 30/360 là: 23/360 = 0,064. Lãi tính trong thời gian này của khoản nợ 1,33 tỷ với lãi suất 15%/năm là: (23/360)*15%*1.330 = 0,064*15%*1.330 = 12,75 triệu VND. Tương tự, theo hệ actual/360, khoản lãi bằng: (24/360)*15%*1.330 = 0,067*15%*1.330 = 13,30 triệu VND. Theo hệ actual/365, khoản lãi bằng: (24/365)*15%*1.330 = 0,066*15%*1.330 = 13,12 triệu VND. Tần suất thanh toán là khoảng thời gian định kỳ phải trả nợ gốc và lãi. Tần suất thanh toán có thể là tháng, quý (3 tháng), bán niên (6 tháng) hay năm (12 tháng). Ở ô B7, ta nhập giá trị 1 nếu tần suất thanh toán là hàng tháng, 3 nếu là hàng quý, 6 nếu là bán niên và 12 nếu là hàng năm.2 Sau cùng, đơn vị tiền tệ được nhập vào ô B8 để biết những con số thể hiện giá trị tiền tệ trong mô hình là được tính theo đồng tiền gì (đồng, đô-la hay euro) và đơn vị bao nhiêu (hàng trăm, nghìn, triệu hay tỷ). Hình 1: Thời gian và đơn vị Nhập thông tin nợ Khung thông tin đầu vào thứ hai là đặc điểm của khoản nợ. Nhập tựa đề “THÔNG TIN NỢ” vào ô A10 và định dạng khung cho diện tích A10:B19. Sau đó, từ ô A11 đến A19, nhập lần lượt các tựa đề: “Mô tả khoản nợ”, “Dư nợ gốc”, “Hình thức lãi suất”, “Lãi suất cố định”, “Đường lãi suất thả nổi”, “Chênh lệch lãi suất”, “Kỳ điều chỉnh lãi suất”, “Kỳ hạn” và “Hình thức trả nợ”. Nhập “Vay mua nhà” vào ô B11 và giá trị nợ vay ban đầu 1.330.000.000 3 vào ô B12. Các ô từ B13 đến B17 là thông tin về lãi suất của khoản vay. Ở ô B13, ta nhập giá trị 1 nếu khoản vay có lãi suất cố định và 2 nếu khoản vay có lãi suất thả nổi. Trên thực tế, khoản vay có lãi suất thả nổi với lãi suất 3 tháng đầu được cố định ở mức 15%. Ta nhập 15% vào ô B14 để xem xét trường hợp lãi suất được cố dịnh ở mức này trong suốt kỳ hạn của khoản vay. 2 Lưu ý rằng mô hình quy ước thời điểm ngân lưu là vào cuối kỳ. 3 Ta phải nhập con số này vì đơn vị tiền tệ trong ô A8 là đồng. Còn nếu ô A8 là triệu đồng, thì ta phải nhập 1330. Nguyễn Xuân Thành 2
3. Đối với hợp đồng vay nợ có lãi suất thả nổi, mức lãi suất áp dụng sẽ bằng một mức lãi suất tham chiếu trên thị trường tiền tệ cộng với một khoản chênh lệch lãi suất và được điều chỉnh theo định kỳ. Trước hết ta phải sử dụng các dự báo lãi suất thị trường trong kỳ hạn của khoản vay để tính lãi suất tham chiếu. Lãi suất tham chiếu có thể là lãi suất tiền gửi, lãi suất liên ngân hàng, lãi suất cho vay bình quân của các ngân hàng, Ở ô B15, ta nhập giá trị 1 để tham chiếu đến loại lãi suất thị trường thứ nhất, 2 để tham chiếu đến loại lãi suất thị trường thứ hai, Ở ô B16, ta nhập giá trị chênh lệch lãi suất áp dụng cho khoản vay. Khoản vay được điều chỉnh lãi suất 3 tháng 1 lần nên ô B17 có giá trị 3. Ô B18 và B19 cho thông tin về kỳ hạn và hình thức trả nợ gốc. Khoản vay đáo hạn vào 1/3/2018 với 96 kỳ trả lãi và nợ gốc. Ô B18 có giá trị 96. Nợ gốc có thể được trả theo nhiều hình thức khác nhau. Trong ô B19, giá trị 1 tương ứng với hình thức nợ gốc và lãi được trả đều định kỳ, 2 tương ứng với nợ gốc được trả đều và 3 là toàn bộ nợ gốc được trả một lần khi đáo hạn. Hình 2: Thông tin nợ Thiết lập chuỗi thời gian của ngân lưu Nhóm thông tin tiếp theo cần thiết lập là ngân lưu nợ vay theo thời gian. Nhập tiêu đề “NGÂN LƯU NỢ VAY” vào ô A21. Dòng 22 được sử dụng làm tiêu đề cho các chuỗi thời gian và ngân lưu. Từ ô A22 đến C22, lần lượt nhập “Kỳ”, “Ngày” và “Hệ số ngày”. Từ ô E22 đến J22, lần lượt nhập “Dư nợ đầu kỳ”, “Lãi suất”, “Trả lãi”, “Trả nợ gốc”, “Trả nợ gốc và lãi” và “Dư nợ cuối kỳ”. Hình 3: Chuỗi thời gian của ngân lưu nợ vay Giá trị nhập trong Cột A với tiêu đề “Kỳ” là để chỉ thứ tự các kỳ thanh toán. Xuất phát điểm là kỳ 0 tại ô A23 với giá trị nhập là 0. Ô A24 ở ngay phía dưới là kỳ kế tiếp. Trong ô A24, ta nhập công thức: =A23+1 Với kỳ hạn 8 năm, khoản vay có 96 kỳ thanh toán. Dùng tính năng copy của Excel để chép công thức trong ô A24 vào các ô A25:A119. Ô A119 sẽ phải có giá trị bằng 96. Nguyễn Xuân Thành 3
4. Cột B là cột ngày, tương ứng với thời điểm cuối kỳ trong cột A. Định dạng các ô B23:B119 theo dd/mm/yyyy tương tự như ô B4 và B5. Ta quy ước cuối kỳ 0 là thời điểm hoàn tất giải ngân (8/3/2011 tại ô A4) và lãi bắt đầu được tính từ thời gian đó. Kỳ tiếp theo theo là kỳ thanh toán đầu tiên (1/4/2010 tại ô A5). Từ đó trở đi, kỳ sau sẽ cách kỳ trước một khoảng thời gian đúng như thông tin trong ô A7. Vậy, để tính tự động, ta phải nhập công thức cho các ô trong cột B để tính ngày. Phần đầu của công thức trong ô B23 sẽ được nhập như sau: =IF(A23=0,$B$4,IF(A23=1,$B$5, Công thức trên có nghĩa là nếu tham chiếu thấy kỳ tương ứng ở cột A có giá trị bằng 0, thì giá trị ở cột B phải là ngày giải ngân, còn nếu kỳ tương ứng ơ cột A có giá trị bằng 1 thì giá trị ở cột B phải là ngày thanh toán thứ nhất. Lưu ý rằng ta phải cố định ô B4 và B5 trong công thức bằng kỳ hiệu $ vì giá trị của các ô này là cố định nên sẽ không đổi khi ta chép công thức sang các ô khác trong cột B. Để thể hiện việc các kỳ sau có giá trị tăng dần đều theo như tần suất thanh toán, ta dùng hàm EDATE của Excel với định dạng: EDATE(start_date, months) Hàm EDATE cho kết quả là ngày cuối kỳ căn cứ vào start_date là ngày cuối của kỳ trước đó (nhập theo số serial) và months là số tháng giữa hai kỳ (tức là tần suất thanh toán). Ví dụ, nếu ngày cuối của kỳ trước là 1/7/2010 (ứng với số serial là 40360), thì để thể hiện ngày cuối của kỳ tiếp theo, ta nhập công thức sau: =EDATE(40360,1) = 1/8/2010 Công thức đầy đủ nhập trong ô B23 là: =IF(A23=0,$B$4,IF(A23=1,$B$5,EDATE(B22,$B$7))) Dùng tính năng copy của Excel để chép công thức trong ô B23 vào các ô B24:B119. Ô B119 phải có giá trị 01/03/2018, ngày đáo hạn của khoản vay. Việc dùng công thức tính ngày như trên có vẻ quá phức tạp so với việc nhập giá trị một cách thủ công, nhưng sẽ rất hữu ích vì các giá trị sẽ tự động cập nhật khi ta thay đổi ngày giải ngân, ngày thanh toán thứ nhất hay tần suất thanh toán. Để kiểm chứng, giả sử ta thay đổi tần suất thanh toán từ hàng tháng sang hàng quý bằng cách đổi giá trị trong ô B7 thành 3 (nhưng vẫn giữ ngày giải ngân và ngày thanh toán thứ nhất không đổi). Giá trị ngày ứng với các kỳ của bảng ngân lưu sẽ thay đổi giống như trong Hình 4. Hình 4: Ngày thay đổi theo tần suất thanh toán Cột C của bảng ngân lưu là hệ số ngày, trong đó cho biết số tỷ số giữa số ngày giữa hai kỳ kế tiếp và số ngày trong năm. Như trình bày ở trên, hệ số ngày phụ thuộc vào hệ đếm ngày áp dụng trong mô hình. Nguyễn Xuân Thành 4
5. Với hệ 30/360, ta dùng hàm DAYS360 để tính số ngày giữa hai kỳ kế tiếp với định dạng: DAYS360(start_date,end_date) Ví dụ, số ngày giữa kỳ 0 và kỳ 1 theo hệ 30/360 là 23 tính theo công thức: =DAYS360(B23,B24) Với hệ actual/360 hay actual/365, ta lấy hiệu số giữa hai ngày cuối của hai kề kế tiếp để tính số ngày thực tế. Ví dụ, số ngày giữa kỳ 0 và kỳ 1 theo hệ actual/360 là 24 theo công thức: =B24 B23 Trong ô C24, ta nhập công thức: = IF($B$6=1,DAYS360(B23,B24)/360,IF($B$6=2,(B24 B23)/360,(B24 B23)/365)) Chép công thức trong ô C24 sang các ô C25:C119. Với hệ 30/360, các ô C25:C119 đều phải có giá trị bằng 0,083. Tức là khoảng thời gian giữa hai kỳ kế tiếp là 0,083 năm. Dư nợ đầu kỳ và cuối kỳ Các ô E22 đến J22 chứa tiêu đề của các ngân lưu trong lịch trả nợ. Dư nợ cuối kỳ 0, tức là thời điểm ngay sau khi giải ngân, luôn bằng đúng giá trị nợ gốc. Ở ô J23, ta nhập một phần công thức: =IF(A23=0,$B$12, Công thức trên có nghĩa là nếu ô tương ứng trong cột A có giá trị bằng 0 (kỳ 0), thì dư nợ cuối kỳ bằng 1.330.000.000 đồng. Đẳng thức thứ nhất trong lịch trả nợ áp dụng cho những kỳ không phải kỳ 0 là: Dư nợ cuối kỳ = Dư nợ đầu kỳ Trả nợ gốc Theo đẳng thức trên, ta hoàn chỉnh công thức trong ô J23 như sau: IF(A23=0,$B$12,E23 H23) Phần bổ sung có nghĩa là nếu không phải là kỳ 0, thì dư nợ cuối kỳ bằng dư nợ đầu kỳ trừ đi trả nợ gốc trong kỳ. Chép công thức trong ô J23 sang các ô J24:J119. Đẳng thức thứ hai trong lịch trả nợ là: Dư nợ đầu kỳ = Dư nợ cuối kỳ trước. Theo đẳng thức trên, ta nhập công thức trong ô E24 như sau: =J23 Chép công thức trong ô E24 sang các ô E25:E119. Lãi suất Việc lập công thức tính lãi suất/năm trong các ô của cột F phụ thuộc vào hình thức lãi suất, đường lãi suất thả nổi và tần suất điều chỉnh lãi suất. Nguyễn Xuân Thành 5
6. Khi khoản vay có lãi suất thả nổi (ô B13=2), để thiết lập ngân lưu ta phải sử dụng một hay nhiều nguồn thông tin dự báo lãi suất thị trường trong tương lai. Chuyển sang bảng tính “Rate” để nhập thông tin về các đường lãi suất thả nổi. Ở ô A1, nhập tựa đề “Đường lãi suất thả nổi”. Toàn bộ các ô A2:B99 được tham chiếu tương ứng từ các ô A22:B119 trong bảng tính “Model”. Ở các ô D2:F2, ta nhập lần lượt các giá trị 1, 2, 3 để chỉ đường lãi suất thả nổi 1, 2, 3 ứng với giá trị nhập trong ô B15 của bảng tính “Model”. Trong Hình 5 dưới đây, ô D4:D24 thể hiện một dự báo lãi suất tiền gửi ngân hàng kỳ hạn 1 năm trong năm 2010 và 2011. Hãy nhập các giá trị này vào bảng tính của mình, đồng thời giả định rằng lãi suất tiền gửi kỳ hạn 1 năm sẽ nhận giá trị 10% từ tháng 1/2012 trở đi. Hình 5: Đường lãi suất thả nổi Bây giờ, ta quay lại bảng tính “Model” để viết công thức trong ô F24. Với thông tin đầu vào là lãi suất thả nổi (ô B13) căn cứ vào đường lãi suất thả nổi thứ nhất (ô B15), ta muốn Excel tham chiếu tới ô D4 tại bảng tính “Rate” để đưa giá trị của ô này vào ô F24 tại bảng tính “Model”. Để làm điều này, ta sử dụng hàm INDEX của Excel với định dạng: INDEX(array,row_num,column_num) Hàm INDEX sẽ tìm một giá trị nhất định bằng cách nhìn vào khung khai báo bởi array, rồi tham chiếu đến hàng có giá trị bằng row-num và cột có giá trị column_num của khung này. Cụ thể, để tham chiếu giá trị 10,80% tại ô D4 trong bảng tính “Rate”, ta khai báo công thức sau trong ô F24: =INDEX(Rate!$D$4:$F$363,A24,$B$15) Tuy nhiên, công thức trên cho lãi suất thả nổi là lãi suất tiền gửi kỳ hạn một năm. Lãi suất áp dụng cho khoản vay bằng lãi suất tiền gửi cộng với khoản chênh lệch lãi suất 4,2% (đã nhập ở ô B16). Do vậy, ta điều chỉnh công thức như sau: =INDEX(Rate!$D$4:$F$363,A24,$B$15)+$B$16 Công thức trên chỉ đúng khi lãi suất thả nổi trong hợp đồng vay nợ được điều chỉnh định kỳ theo đúng như kỳ trả nợ. Đối với khoản vay đang xem xét, kỳ trả nợ là hàng tháng, trong khi 3 tháng (theo thông tin trong ô B17) mới điều chỉnh lãi suất một lần. Cụ thể, lãi suất sẽ được điều chỉnh vào các kỳ 4, 7, 10, 13, Lãi suất trong những kỳ còn lại sẽ bằng lãi suất của kỳ Nguyễn Xuân Thành 6
7. trước đó cho dù lãi suất thị trường có thay đổi. Để tính đến tần suất điều lãi suất này, ta sử dụng hàm MOD với định dạng: MOD(number,divisor) Hàm MOD cho kết quả là phần dư trong phép chia số chia number cho số bị chia divisor. Như vậy, nếu một kỳ nhất định có số thứ tự trừ 1 chia hết cho 3, thì ta phải tham chiếu lãi suất thả nổi ở bảng tính “Rate”, còn nếu không thì ta tham chiếu lãi suất áp dụng ở kỳ trước. Công thức tham chiếu lãi suất thả nổi được bổ sung như sau: =IF(MOD(A24 1,$B$17)=0,INDEX(Rate!$D$4:$F$363,A24,$B$15)+$B$16,F23) Hình 6: Tham chiếu lãi suất thả nổi Nếu lãi suất của khoản vay là cố định (ô B13=1), ta nhập một phần công thức tính lãi suất trong ô F24 như sau: =IF($B$13=1,$B$14, Kết hợp với công thức cho trường hợp lãi suất cố định và lãi suất thả nổi đối với ô F24, ta có =IF($B$13=1,$B$14,IF(MOD(A24 1,$B$17)=0, INDEX(Rate!$D$4:$F$363,A24,$B$15)+$B$16,F23)) Chép công thức trong ô F24 sang các ô F25:F119. Trả lãi Lãi phải trả vào cuối kỳ được tính căn cứ vào dư nợ đầu kỳ và lãi suất theo kỳ. Lãi suất theo kỳ được tính bằng lãi suất trên năm và hệ số ngày của kỳ trên năm. Đẳng thức thứ ba trong lịch trả nợ là: Trả lãi = Dư nợ đầu kỳ × Lãi suất/năm × Hệ số ngày Theo đẳng thức trên, ta nhập công thức trong ô G24 như sau: =E24*F24*C24 Chép công thức trong ô G24 sang các ô G25:G119. Nguyễn Xuân Thành 7
8. Trả nợ gốc Trả nợ gốc phụ thuộc vào hình thức trả nợ khai báo trong ô B19. Nếu ô B19 có giá trị 1, nợ gốc cộng lãi được trả đều hàng kỳ. Ta dùng hàm PMT của Excel để tính khoản thanh toán gốc và lãi (sẽ được trình bày ở mục sau). Đẳng thức thứ tư trong lịch trả nợ là: Trả lãi + Trả nợ gốc = Trả nợ gốc và lãi Vậy, trong ô H24, ta nhập một phần công thức như sau: = IF($B$19=1,I24 G24, Công thức trên có nghĩa là nếu nợ gốc cộng lãi được trả đều thì giá trị trả nợ gốc sẽ bằng nợ gốc và lãi được trả đều trừ đi phần trả lãi. Con số 2 trong ô B19 cho biết khoản vay có nợ gốc được trả đều hàng kỳ. Khi đó giá trị nợ gốc trả đều bằng dư nợ ban đầu chia cho số kỳ trả lãi. Ta bổ sung công thức trong ô H24 như sau: =IF($B$19=1,I24 G24,IF($B$19=2,$B$12/$B$18, Phần bổ sung có nghĩa là nếu nợ gốc được trả đều thì khoản nợ gốc phải định kỳ sẽ bằng dự nợ ban đầu (1,33 tỷ VND) chia cho 96 kỳ. Nếu ô B19 bằng 3 thì khoản vay có nợ gốc được trả một lần khi đáo hạn. Công thức hoàn chỉnh trong ô H24 là: =IF($B$19=1,I24 G24,IF($B$19=2,$B$12/$B$18,IF(A24<$B$18,0,$B$12))) Phần bổ sung có nghĩa là đối với trường hợp trả nợ gốc một lần khi đáo hạn, nếu kỳ xem xét nhỏ hơn kỳ cuối cùng thì giá trị trả nợ gốc bằng 0 và giá trị trả nợ gốc vào kỳ cuối cùng bằng dư nợ ban đầu. Chép công thức trong ô H24 sang các ô H25:H119. Trả nợ gốc và lãi Để tính nợ gốc và lãi được trả đều hàng kỳ (khi B19=1), ta dùng hàm PMT của Excel với định dạng: PMT(rate,nper,pv) Hàm PMT cho giá trị thanh toán (gồm cả nợ gốc và lãi) đều hàng kỳ căn cứ vào lãi suất theo kỳ (rate), số kỳ (nper) và dư nợ ban đầu (pv). Với dư nợ ban đầu dương, hàm PMT cho giá trị âm để thể hiện đây là một khoản phải trả. Ví dụ, khoản vay có lãi suất 15%/năm phải được chuyển thành lãi suất/tháng là 15%*0,083 = 1,25%; số kỳ là 96 tháng và dư nợ ban đầu 1,33 tỷ VND. Ta có: PMT(1.25%,96,1330000000) = -23.867.389 Trong trường hợp khoản vay có lãi suất cố định thì giá trị trên sẽ không đổi trong tất cả các kỳ trả nợ. Tuy nhiên, đối với trường hợp lãi suất thả nổi, thì giá trị trả nợ và lãi trong mỗi kỳ sẽ thay đổi tuỳ theo sự thay đổi của lãi suất, kỳ hạn còn lại và dư nợ đầu kỳ. Ta nhập công thức trong ô I24 như sau: Nguyễn Xuân Thành 8
9. =IF($B$19=1,-PMT(F24*C24,$B$18 A23,E24),H24+G24) Công thức trên có nghĩa là nếu nợ gốc và lãi được trả đều, thì giá trị trả nợ hàng kỳ được tính theo hàm PMT, còn theo hình thức khác thì giá trị trả nợ bằng trả lãi cộng trả nợ gốc. Trong hàm PMT, F24*C24 là lãi suất điều chỉnh theo kỳ, $B$18 A23 là kỳ hạn còn lại của khoản vay và E24 là dư nợ đầu kỳ. Tuy nhiên, vào kỳ cuối cùng, nợ gốc đầu kỳ có thể nhỏ hơn giá trị nợ gốc và lãi trả đều tính theo hàm PMT. Khi đó Excel sẽ tự động cho giá trị dư nợ cuối kỳ âm. Trên thực tế, vào kỳ cuối cùng thì nợ gốc phải trả bằng đúng dự nợ đầu kỳ để dư nợ cuối kỳ bằng 0. Để xử lý vấn đề này ta đều chỉnh lại công thức trong ô I24 như sau: =IF($B$19=1,IF(E24<I23,E24+G24, PMT(F24*C24,$B$18 A23,E24)),H24+G24) Sau cùng, chép công thức trong ô I24 sang các ô I25:I119. Lịch trả nợ sẽ có kết quả như các hình dưới đây (cho trường hợp lãi suất cố định). Hình 7: Nợ gốc và lãi trả đều Hình 8: Nợ gốc trả đều Hình 9: Nợ gốc trả một lần khi đáo hạn Đến đây, mô hình ngân lưu nợ vay trên Excel đã gần như được hoàn chỉnh. Tuy nhiên, ta có thể thấy rằng khi điều chỉnh kỳ hạn của nợ vay (thông qua ô B18) thì bảng ngân lưu nợ vay không tự điều chỉnh. Nếu kỳ hạn thấp hơn 96 thì ta phải xoá bớt dòng, còn nếu kỳ hạn dài hạn 96 thì ta phải chép thêm dòng một cách thủ công. Để làm tự động, ta quy định rằng mô hình chỉ phân tích khoản vay với số kỳ hạn tối đa là 360. Như vậy, nếu kỳ hạn là tháng, thì ta có thể phân tích một khoản vay với kỳ hạn dài nhất là 30 năm. Trong bảng tính “Model”, ta định dạng chuỗi thời gian và bảng ngân lưu gồm kỳ 0 và 360 kỳ tiếp theo từ dòng 23 đến dòng 383. Công thức được chép cho toàn bộ các dòng này ứng với các cột chuỗi thời gian và ngân lưu. Tương tự, trong bảng tính “Rate”, ta định dạng chuỗi thời gian và bảng đường lãi suất thả nổi từ dòng 3 đến dòng 363. Để bảng tính thay đổi tự động theo kỳ hạn khoản vay, ta điều chỉnh các công thức đã thiết lập trong bảng Ngân lưu nợ vay sao cho đối với một ô nhất định, nếu kỳ ứng với ô đó nằm trong kỳ hạn của khoản vay, thì giá trị của ô được hiển thị, còn nếu nằm ngoài kỳ hạn thì giá trị Nguyễn Xuân Thành 9
10. không được hiện thị. Ví dụ, đối với khoản vay đang xem xét có kỳ hạn 96 tháng tại bảng tính “Model”, giá trị ở các dòng 23 đến 119 (ứng với kỳ 0-96) sẽ hiển thị, còn các ô trong dòng 120 đến 383 sẽ không hiển thị. Ta dùng hàm IF như sau: IF(Kỳ>$B$18,"",Công thức ban đầu) Công thức trên có nghĩa là nếu kỳ của ô tương ứng lớn hơn kỳ hạn khoản vay, thì sẽ không hiện thị giá trị (""), còn nếu không thì hiện thị giá trị của công thức ban đầu. Vậy, đối với tất cả các ô B24:J383 của bảng tính “Model”, ta bổ sung công thức như sau: =IF(Axx>$B$18,"",Công thức ban đầu) Trong công thức trên, A là cột A và xx là số thứ tự của dòng tương ứng với ô đang điều chỉnh công thức. Ví dụ, ta điều chỉnh công thức trong ô B27 thành: =IF(A27>$B$18,"",IF(A27=0,$B$4,IF(A27=1,$B$5,EDATE(B26,$B$7)))) Sau khi điều chỉnh công thức cho tất cả các ô B24:J383, chúng ta hoàn chỉnh mô hình. Nguyễn Xuân Thành 10