{"id":9978,"date":"2025-01-06T11:25:20","date_gmt":"2025-01-06T11:25:20","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/06\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel\/"},"modified":"2025-01-06T11:25:20","modified_gmt":"2025-01-06T11:25:20","slug":"huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/06\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m ISPMT t\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a kho\u1ea3n vay tr\u00ean Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Sau khi \u0111\u1ecdc b\u00e0i vi\u1ebft \u201c<a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-lap-bang-tinh-lai-suat-vay-tieu-dung-theo-du-no-giam-dan-tren-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch l\u1eadp b\u1ea3ng t\u00ednh l\u00e3i su\u1ea5t vay ti\u00eau d\u00f9ng theo d\u01b0 n\u1ee3 gi\u1ea3m d\u1ea7n tr\u00ean Excel<\/a>\u201d ch\u1eafc h\u1eb3n ch\u00fang ta\u00a0\u0111\u1ec1u th\u1ea5y vi\u1ec7c ph\u00e2n t\u00edch 1 d\u1ef1\u00a0\u00e1n t\u00e0i ch\u00ednh (hay 1 kho\u1ea3n\u00a0\u0111\u1ea7u t\u01b0, 1 kho\u1ea3n vay) tr\u00ean\u00a0Excel kh\u00e1 th\u00fa v\u1ecb ph\u1ea3i kh\u00f4ng n\u00e0o. V\u1eady b\u1ea1n c\u00f3 bi\u1ebft\u00a0Excel c\u00f2n cung c\u1ea5p cho ch\u00fang ta nh\u1eefng h\u00e0m t\u00e0i ch\u00ednh ri\u00eang\u00a0\u0111\u1ec3\u00a0\u00e1p d\u1ee5ng cho nh\u1eefng vi\u1ec7c n\u00e0y kh\u00f4ng? H\u00e3y c\u00f9ng H\u1ecdc\u00a0Excel Online t\u00ecm hi\u1ec3u v\u1ec1\u00a0h\u00e0m ISPMT \u0111\u1ec3 t\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a kho\u1ea3n vay tr\u00ean Excel nh\u00e9.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-677bbd9f4ed5a\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel.html#Y_nghia_va_cau_truc_cua_ham_ISPMT\" title=\"\u00dd ngh\u0129a v\u00e0 c\u1ea5u tr\u00fac c\u1ee7a h\u00e0m\u00a0ISPMT\">\u00dd ngh\u0129a v\u00e0 c\u1ea5u tr\u00fac c\u1ee7a h\u00e0m\u00a0ISPMT<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel.html#Cau_truc_cua_ham_nhu_sau\" title=\"C\u1ea5u tr\u00fac c\u1ee7a h\u00e0m nh\u01b0 sau:\">C\u1ea5u tr\u00fac c\u1ee7a h\u00e0m nh\u01b0 sau:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel.html#Nhung_luu_y_khi_su_dung_ham_ISPMT\" title=\"Nh\u1eefng l\u01b0u\u00a0\u00fd khi s\u1eed d\u1ee5ng h\u00e0m\u00a0ISPMT:\">Nh\u1eefng l\u01b0u\u00a0\u00fd khi s\u1eed d\u1ee5ng h\u00e0m\u00a0ISPMT:<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-su-dung-ham-ispmt-tinh-tien-lai-cua-khoan-vay-tren-excel.html#Ap_dung_ham_ISPMT_vao_tinh_tien_lai_cua_khoan_vay_tren_Excel\" title=\"\u00c1p d\u1ee5ng h\u00e0m\u00a0ISPMT v\u00e0o\u00a0t\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a kho\u1ea3n vay tr\u00ean Excel\">\u00c1p d\u1ee5ng h\u00e0m\u00a0ISPMT v\u00e0o\u00a0t\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a kho\u1ea3n vay tr\u00ean Excel<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Y_nghia_va_cau_truc_cua_ham_ISPMT\"><\/span>\u00dd ngh\u0129a v\u00e0 c\u1ea5u tr\u00fac c\u1ee7a h\u00e0m\u00a0ISPMT<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Nh\u1eafc\u00a0\u0111\u1ebfn h\u00e0m\u00a0ISPMT, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y ngay ph\u1ea7n PMT trong t\u00ean h\u00e0m,\u00a0\u0111i\u1ec1u n\u00e0y c\u00f3\u00a0\u00fd ngh\u0129a r\u1eb1ng h\u00e0m n\u00e0y thu\u1ed9c nh\u00f3m h\u00e0m PMT, d\u00f9ng cho vi\u1ec7c ph\u00e2n t\u00edch tr\u1ea3 n\u1ee3 g\u1ed1c v\u00e0 l\u00e3i.<\/p>\n<p style=\"padding-left: 30px\">Tham kh\u1ea3o:\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/excel-tai-chinh-phan-tich-tra-no-goc-va-lai-cung-chuoi-ham-pmt.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch ph\u00e2n t\u00edch tr\u1ea3 n\u1ee3 g\u1ed1c v\u00e0 l\u00e3i c\u00f9ng chu\u1ed7i h\u00e0m PMT trong Excel<\/a><\/p>\n<p>C\u1ee5 th\u1ec3 h\u00e0m\u00a0ISPMT c\u00f3 t\u00e1c d\u1ee5ng: T\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a 1 kho\u1ea3n vay (hay kho\u1ea3n\u00a0\u0111\u1ea7u t\u01b0) theo nguy\u00ean t\u1eafc d\u01b0 n\u1ee3 gi\u1ea3m d\u1ea7n.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cau_truc_cua_ham_nhu_sau\"><\/span>C\u1ea5u tr\u00fac c\u1ee7a h\u00e0m nh\u01b0 sau:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"padding-left: 30px\"><strong>ISPMT(rate, per, nper, pv)<\/strong><\/p>\n<p>Trong\u00a0\u0111\u00f3:<\/p>\n<ul>\n<li>Rate l\u00e0 l\u00e3i su\u1ea5t c\u1ee7a kho\u1ea3n vay (hay kho\u1ea3n\u00a0\u0111\u1ea7u t\u01b0). L\u00e3i su\u1ea5t ph\u1ea3i quy\u00a0\u0111\u1ed5i th\u1ed1ng nh\u1ea5t\u00a0\u0111\u01a1n v\u1ecb t\u00ednh theo k\u1ef3<\/li>\n<li>Per:\u00a0K\u1ef3 t\u00ednh l\u00e3i. K\u1ef3 n\u00e0y b\u1eaft\u00a0\u0111\u1ea7u t\u00ednh t\u1eeb 0 ch\u1ee9 kh\u00f4ng ph\u1ea3i t\u1eeb 1<\/li>\n<li>Nper: T\u1ed5ng s\u1ed1 k\u1ef3 c\u1ee7a kho\u1ea3n vay\u00a0(hay kho\u1ea3n\u00a0\u0111\u1ea7u t\u01b0)<\/li>\n<li>PV: Gi\u00e1 tr\u1ecb hi\u1ec7n t\u1ea1i c\u1ee7a kho\u1ea3n\u00a0\u0111\u1ea7u t\u01b0 (hay s\u1ed1 ti\u1ec1n vay\u00a0\u0111\u01b0\u1ee3c gi\u1ea3i ng\u00e2n)<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Nhung_luu_y_khi_su_dung_ham_ISPMT\"><\/span>Nh\u1eefng l\u01b0u\u00a0\u00fd khi s\u1eed d\u1ee5ng h\u00e0m\u00a0ISPMT:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ul>\n<li>K\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m l\u00e0 s\u1ed1\u00a0\u00e2m<\/li>\n<li>Ph\u1ea3i th\u1ed1ng nh\u1ea5t v\u1ec1\u00a0\u0111\u01a1n v\u1ecb t\u00ednh k\u1ef3 trong Rate, Per, Nper<\/li>\n<li>Per lu\u00f4n ph\u1ea3i t\u00ednh t\u1eeb 0<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Ap_dung_ham_ISPMT_vao_tinh_tien_lai_cua_khoan_vay_tren_Excel\"><\/span>\u00c1p d\u1ee5ng h\u00e0m\u00a0ISPMT v\u00e0o\u00a0t\u00ednh ti\u1ec1n l\u00e3i c\u1ee7a kho\u1ea3n vay tr\u00ean Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Tr\u1edf l\u1ea1i\u00a0b\u00e0i vi\u1ebft \u201c<a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-lap-bang-tinh-lai-suat-vay-tieu-dung-theo-du-no-giam-dan-tren-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch l\u1eadp b\u1ea3ng t\u00ednh l\u00e3i su\u1ea5t vay ti\u00eau d\u00f9ng theo d\u01b0 n\u1ee3 gi\u1ea3m d\u1ea7n tr\u00ean Excel<\/a>\u201c,\u00a0\u0111\u00e2y l\u00e0 1 b\u00e0i to\u00e1n m\u00e0 ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m\u00a0ISPMT\u00a0\u0111\u1ec3 t\u00ednh cho c\u1ed9t Tr\u1ea3 l\u00e3i m\u00e0 kh\u00f4ng c\u1ea7n ph\u1ee5 thu\u1ed9c v\u00e0o c\u1ed9t N\u1ee3\u00a0\u0111\u1ea7u k\u1ef3.<\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-117.gif\" title=\"ex101_92019-nho-117\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>T\u1ea3i file m\u1eabu k\u00e8m theo b\u00e0i vi\u1ebft\u00a0 tr\u00ean t\u1ea1i\u00a0\u0111\u1ecba ch\u1ec9:\u00a0<a href=\"http:\/\/bit.ly\/2N4p19Q\">http:\/\/bit.ly\/2N4p19Q<\/a><\/p>\n<p>T\u1ea1i c\u1ed9t H, ch\u00fang ta s\u1ebd t\u00ednh ph\u1ea7n Tr\u1ea3 l\u00e3i theo h\u00e0m\u00a0ISPMT nh\u01b0 sau:<\/p>\n<ul>\n<li>\u00d4 H14\u00a0\u0111\u1eb7t c\u00f4ng th\u1ee9c\u00a0ISPMT<\/li>\n<li>Rate = gi\u00e1 tr\u1ecb\u00a0\u00f4 G5 (l\u00e3i su\u1ea5t quy ra th\u00e1ng\u00a0\u0111\u1ec3 t\u01b0\u01a1ng\u00a0\u1ee9ng theo h\u00ecnh th\u1ee9c tr\u1ea3 l\u00e3i l\u00e0 theo Th\u00e1ng =\u00a0\u00f4 B4)<\/li>\n<li>Per l\u00e0 k\u1ef3 t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi d\u00f2ng 14. Tuy nhi\u00ean k\u1ef3 x\u00e9t t\u1eeb 0, trong khi c\u1ed9t A th\u00ec k\u1ef3 b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb 1, do\u00a0\u0111\u00f3 ch\u00fang ta s\u1ebd tr\u1eeb\u00a0\u0111i 1\u00a0\u0111\u01a1n v\u1ecb<\/li>\n<li>Nper l\u00e0 t\u1ed5ng s\u1ed1 k\u1ef3\u00a0\u0111\u00e3 quy\u00a0\u0111\u1ed5i v\u1ec1 k\u1ef3 theo th\u00e1ng =\u00a0\u00f4 G3<\/li>\n<li>PV l\u00e0 s\u1ed1 ti\u1ec1n vay t\u1ea1i\u00a0\u00f4 B2<\/li>\n<\/ul>\n<p>Ta c\u00f3 =ISPMT($G$5,A14-1,$G$3,$B$2)<\/p>\n<p>V\u00ec h\u00e0m cho ra k\u1ebft qu\u1ea3 l\u00e0 s\u1ed1\u00a0\u00e2m, n\u00ean ch\u00fang ta c\u00f3 th\u1ec3 chuy\u1ec3n v\u1ec1 s\u1ed1 d\u01b0\u01a1ng b\u1eb1ng c\u00e1ch nh\u00e2n v\u1edbi -1<\/p>\n<p style=\"padding-left: 30px\">H14=ISPMT($G$5,A14-1,$G$3,$B$2)*(-1)<\/p>\n<p>Ti\u1ebfp theo Filldown c\u00f4ng th\u1ee9c t\u1eeb H14 xu\u1ed1ng c\u00e1c d\u00f2ng c\u00f2n l\u1ea1i, ta c\u00f3 k\u1ebft qu\u1ea3 gi\u1ed1ng v\u1edbi c\u00e1ch t\u00ednh\u00a0\u1edf c\u1ed9t D, nh\u01b0 v\u1eady l\u00e0 ch\u00fang ta\u00a0\u0111\u00e3 t\u00ednh\u00a0\u0111\u00fang.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-22445\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/su-dung-ham-ispmt-tinh-lai-vay-giam-dan.png\" width=\"1080\" height=\"683\" title=\"su-dung-ham-ispmt-tinh-lai-vay-giam-dan\"><\/p>\n<p>Nh\u01b0 v\u1eady ch\u00fang ta c\u00f3 th\u1ec3 t\u00ednh ngay ra c\u1ed9t l\u00e3i vay b\u1eb1ng h\u00e0m\u00a0ISPMT, thay v\u00ec ph\u1ea3i x\u00e1c\u00a0\u0111\u1ecbnh c\u1ee5 th\u1ec3\u00a0 c\u1ed9t N\u1ee3\u00a0\u0111\u1ea7u k\u1ef3<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 t\u1ea3i v\u1ec1 file m\u1eabu t\u1ea1i\u00a0\u0111\u1ecba ch\u1ec9: <a href=\"http:\/\/bit.ly\/2R4CNwu\">http:\/\/bit.ly\/2R4CNwu<\/a><\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n\u00a0\u00e1p d\u1ee5ng th\u00e0nh c\u00f4ng h\u00e0m\u00a0ISPMT v\u00e0o vi\u1ec7c ph\u00e2n t\u00edch c\u00e1c d\u1ef1\u00a0\u00e1n t\u00e0i ch\u00ednh c\u1ee7a m\u00ecnh.<\/p>\n<p>Xem th\u00eam:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/ham-tai-chinh-va-cach-su-dung-cac-ham-tai-chinh-trong-excel-full.html\">H\u00e0m t\u00e0i ch\u00ednh v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c h\u00e0m t\u00e0i ch\u00ednh trong Excel, Full<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/excel-voi-tai-chinh-ham-pmt-tinh-tien-thanh-toan-hang-ky-cho-khoan-vay.html\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m PMT t\u00ednh s\u1ed1 ti\u1ec1n thanh to\u00e1n h\u00e0ng k\u1ef3 cho kho\u1ea3n vay trong Excel<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/excel-tai-chinh-phan-tich-tra-no-goc-va-lai-cung-chuoi-ham-pmt.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch ph\u00e2n t\u00edch tr\u1ea3 n\u1ee3 g\u1ed1c v\u00e0 l\u00e3i c\u00f9ng chu\u1ed7i h\u00e0m PMT trong Excel<\/a><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/ex101_92019-nho-117.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":9979,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-9978","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9978","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=9978"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9978\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/9979"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=9978"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=9978"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=9978"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}