Kỹ thuật sử dụng Excel trong tài chính

Kỹ thuật sử dụng Excel trong tài chính Lê Văn Lâm 1 Nội dung • Ma trận • Xử lý ma trận theo mảng • Xử lý dữ liệu bảng • Các hàm tài chính, thống kê và một số hàm khác thường sử dụng 2 Ma trận Ma trận A gồm m hàng và n cột: 11 12 1 21 22 2 1 2 ... ... ( ) ... ... ... ... ... 1,2,..., 1, 2,..., n n mn ij m m mn a a a a a a A a a a a i m j n                3 Ma trận ( ) ( )ij ijA A a a          . Cộng một số với ma trận: . Nhân một số với ma trận: . Cộng hai ma trận (cùng số cột và số hàng) ( ) ( )ij ijA A a a        ( ) ( ) ( )ij ij ij ijA B a b a b     4 Ma trận .Nhân hai ma trận: columnj( ) ( ) ( ) ; ; ij rowi m n m p p n C AB c A B C A B       5 Ma trận . Ma trận hoán vị: 11 12 1 11 21 1 21 22 2 12 22 2' 1 2 1 2 ... ... ... ... ... ... ... ... ... ... ... ... ... ... nm n m n m mn m m mn n n mn a a a a a a a a a a a a A A a a a a a a                           6 Ma trận . Ngoài ra sinh viên có thể tìm hiểu thêm các khái niệm về ma trận vuông, ma trận chéo, ma trận đơn vị, ma trận nghịch đảo, 7 Xử lý ma trận . Xử lý ma trận chủ yếu được sử dụng trong quản lý danh mục. Ma trận trên Excel thực ra là một mảng (array) hình chữ nhật hoặc hình vuông 8 Nhân hoặc cộng 2 ma trận với một số - Cộng 2 ma trận . Ví dụ: Nhân ma trận sau với 6 9 . Bước 1: Chọn vùng cho ma trận kết quả Nhân hoặc cộng 2 ma trận với một số - Cộng 2 ma trận 10 . Bước 2: Khai báo công thức cho ma trận kết quả (nhớ cố định số) Nhân hoặc cộng 2 ma trận với một số - Cộng 2 ma trận 11 . Bước 3: Control + shift + enter Nhân hoặc cộng 2 ma trận với một số - Cộng 2 ma trận 12 Thực hiện thao tác tương tự cho việc cộng 2 ma trận hoặc cộng ma trận với một số Nhân hoặc cộng 2 ma trận với một số - Cộng 2 ma trận 13 Ma trận chuyển vị . Sử dụng hàm transpose Ví dụ: cho ma trận sau 14 Ma trận chuyển vị . Bước 1: chọn vùng cho ma trận chuyển vị (D3:F4) 15 Ma trận chuyển vị . Bước 2: Khai báo hàm transpose 16 Ma trận chuyển vị . Bước 3: Control + shift + enter (không sử dụng phím Enter đơn lẻ) 17 Nhân hai ma trận & ma trận nghịch đảo Nhân 2 ma trận: Tương tự như chuyển vị ma trận nhưng dùng hàm MMult . Cú pháp: = MMult (range1, range2) Nghịch đảo ma trận: hàm Minverse . Cú pháp = Minverse (range) 18 Xử lý dữ liệu theo bảng Ví dụ: Xét 1 dự án gồm 7 năm, chi phí ban đầu của dự án là 1,150. Dòng tiền năm thứ nhất là 234. Tốc độ tăng trưởng của dòng tiền qua các năm là 10%/năm. Lãi suất chiết khấu 15%/năm. Tính NPV và IRR của dự án? 19 Xử lý dữ liệu theo bảng Công thức sử dụng: Tính NPV Tính IRR 71 2 0 2 7 6 1 1 1 0 2 7 ... (1 ) (1 ) (1 ) (1 ) (1 )... (1 ) (1 ) (1 ) CFCF CFNPV CF r r r CF CF g CF gCF r r r                   71 2 0 2 7 6 1 1 1 0 2 7 0 ... (1 ) (1 ) (1 ) (1 ) (1 )0 ... (1 ) (1 ) (1 ) CFCF CFCF IRR IRR IRR CF CF g CF gCF IRR IRR IRR                   20 Xử lý dữ liệu theo bảng . Xử lý bằng Excel, ta có NPV = 101.46 và IRR = 17.6% 21 Xử lý dữ liệu theo bảng Đặt vấn đề: Với bài toán trên, giả sử các dữ kiện khác không thay đổi, nhưng g lần lượt là 0%, 5%, 10% và 15%. Hãy tính lại NPV và IRR? Xử lý: Trong trường hợp này, có thể sử dụng lệnh Data table cho trường hợp bảng 1 chiều (one dimension). 22 Xử lý dữ liệu theo bảng Bước 1. Tạo 1 bảng mới. Đặt NPV và IRR lên dòng trên cùng và đặt biến thay đổi (g) vào cột đầu tiên 23 Xử lý dữ liệu theo bảng Bước 2. Ở dòng thứ 2 của bảng, lần lượt điền công thức của NPV và IRR 24 Xử lý dữ liệu theo bảng Bước 3. Chọn vùng bảng (là vùng trong border) và gọi lệnh Data table: Data/ What if analysis/ Data table. Ở column input cell: điền công thức của biến thay đổi (g) 25 Xử lý dữ liệu theo bảng 26 Xử lý dữ liệu theo bảng Đặt vấn đề: Với bài toán trên, giả sử các dữ kiện khác không thay đổi, nhưng g lần lượt là 0%, 5%, 10% và 15% và r lần lượt là 7%, 10% và 12% Hãy tính lại NPV? Xử lý: Trong trường hợp này, có thể sử dụng lệnh Data table cho trường hợp bảng 2 chiều (two-dimension table). 27 Xử lý dữ liệu theo bảng Bước 1. Tạo bảng như sau: 28 Xử lý dữ liệu theo bảng Bước 2. Tại G22, điền công thức của NPV: 29 Xử lý dữ liệu theo bảng Bước 3. Chọn bảng & gọi lệnh Data table: 30 Xử lý dữ liệu theo bảng 31 Các hàm tài chính, thống kê . Một số hàm tài chính: PV, FV, PMT, Rate, IRR, NPV,NPER,. . Một số hàm thống kê: Varp, stdevp, average, correl, covar, Các hàm này sẽ được tìm hiểu cụ thể trong các buổi sau. Khuyến khích sinh viên tìm hiểu trước. 32 Các hàm hỗ trợ khác . Hàm ngày, tháng: Excel tự “dịch” hàm ngày tháng thành số. Ví dụ bạn gõ vào 1 cell cụm “3Feb2001” 33 Các hàm hỗ trợ khác .Sau đó nhấn Enter, bạn sẽ thấy trên màn hình như sau: 34 Các hàm hỗ trợ khác .Nếu bạn Format cells/Number/General, bạn sẽ thấy số 36925. Số 1 nghĩa là ngày 1/1/1900: 35 Các hàm hỗ trợ khác .Muốn tìm số ngày giữa 2 ngày, chỉ việc trừ 2 ngày đó đi. . Lưu ý: Cell C7 sẽ hiển thị 1 ngày cụ thể. Bạn phải định dạng lại: Format cells/ number/general 36 Các hàm hỗ trợ khác .Các hàm về ngày, tháng, thời gian: Now, Today, Date, Month, Weekday, . Hàm Dateif 37 Các hàm hỗ trợ khác . Hàm large & rank 38 Các hàm hỗ trợ khác . Hàm count, countA & countIf: Count: đếm những cell có số CountA: đếm tất cả các cell CountIf: đếm theo điều kiện cụ thể 39 Các hàm hỗ trợ khác . Hàm count, countA & countIf: Count: đếm những cell có số CountA: đếm tất cả các cell CountIf: đếm theo điều kiện cụ thể 40 Các hàm hỗ trợ khác . Hàm Offset: Giúp chọn khối trong mảng 41 Các hàm hỗ trợ khác . Hàm Vlookup & Hlookup 42 Các hàm hỗ trợ khác . Hàm Index: Lấy một giá trị đơn lẻ ra khỏi mảng 43